user2340426 user2340426 - 24 days ago 8
MySQL Question

Force MySQL to update TIMESTAMP column

I believe I have set my 5.6.17 ver MySQL server to recognize IANA TZ Databases as evidenced by


  • a system_time_zone variable being set to "Pacific Daylight Time"

  • a time_zone variable being set to UTC

  • NOW(), giving me a standard SQL format date time



I thought that that would be sufficient to create an auto updating time stamp field, but, if I create a table via:

CREATE TABLE test (
id SERIAL,
stamp TIMESTAMP,
stuff VARCHAR(255)
);
INSERT INTO test ( stuff ) VALUES ( 'abc' );
SELECT * FROM test;


records seem to be created with NULL in the stamp field:

id stamp stuff
1 NULL abc


I thought that maybe the date gets entered only when doing an update, but when I update:

UPDATE test SET note = 'xyz' WHERE id = 1;


still the stamp is NULL

id stamp stuff
1 NULL xyz


I attempted to change the create as

stamp TIMESTAMP DEFAULT NOW(),


which provides a proper value, but the stamp field remains unchanged when I update (even minutes later).

I also attempted to use

stamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,


which, also, gave me an initial value, but never changed when updating.

Also, attempting to append AUTO_INCREMENT didn't seem to work for me. Did I mention that I'm a MySQL newb?

How do I force my TIMESTAMP field to fill at creation and modify when updated?

Answer

A field with type TIMESTAMP is also just another field without any special properties like auto initialization or update.

DEFAULT CURRENT_TIMESTAMP only sets the current timestamp when you create the row.

You are looking for the property ON UPDATE CURRENT_TIMESTAMP. This will set the timestamp each time you update the row, given that at least one of the row's values actually changes.

For more infos, have a look at the MySQL docs regarding Automatic Initialization and Update for TIMESTAMP.

Bottom line, create your table like this and stamp will always give you the timestamp of the last change:

CREATE TABLE test (
  id SERIAL,
  stamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  stuff VARCHAR(255)
);