mysql timestamp column

Question

Hi,

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

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

Answer 2

IIRC, the first column in a table of type timestamp is set when a record is created and cannot be changed. The 2nd column of type timestamp is set whenever a record is updated. Or vice versa.

I haven't done much MySQL work, so you'll want to verify this.

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.