Is it possible to define a timestamp column in a MySQL table that will automatically be updated every time a field in the same row is modified? Ideally this column should initially be set to the time a row was inserted.
Cheers, Don
Add a comment
|
4 Answers
You can use the timestamp column as other posters mentioned. Here is the SQL you can use to add the column in:
ALTER TABLE `table1` ADD `lastUpdated` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ;
This adds a column called 'lastUpdated' with a default value of the current date/time. When that record is updated (lets say 5 minutes later) that timestamp will automatically update to the current time.
1 Comment
Dónal
The following appears to be sufficient: ALTER TABLE
table1 ADD lastUpdated TIMESTAMP;That is the default functionality of the timestamp column type. However, note that the format of this type is yyyymmddhhmmss (all digits, no colons or other separation).
EDIT: The above comment about the format is only true for versions of MySQL < 4.1... Later versions format it like a DateTime
1 Comment
Mark Biek
According to the MySQL 5.0 docs, the timestamp type is in the same format as datetime (eg 'YYYY-MM-DD HH:MM:SS').
This is what I have observed (MySql 5.7.11) -
The first TIMESTAMP column in the table gets current timestamp as the default value. So, if you do an INSERT or UPDATE without supplying a value, the column will get the current timestamp.
Any subsequent TIMESTAMP columns should have a default value explicitly defined. If you have two TIMESTAMP columns and if you don't specify a default value for the second column, you will get this error while trying to create the table -
ERROR 1067 (42000): Invalid default value for 'COLUMN_NAME'
Comments
A MySQL timestamp is set with creation or update time only if their default value is set as it. ALTER TABLE some_table ADD when TIMESTAMP DEFAULT CURRENT_TIMESTAMP.
Otherwise it works just like a DateTime field, only that it's relative to 1970/01/01 UTC, so it's an absolute point in time not depending on a specific timezone as is DateTime.
