mysql timestamp column

Question

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

Answer 1

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'

Answer 2

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.

Answer 3

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.

Answer 4

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