mysql timestamp column

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

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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.

Method 2

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

Method 3

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’

Method 4

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.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating