Force MySQL to update TIMESTAMP column

Question

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?

https://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html basically `CREATE TABLE t1 ( ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );` — Squeegy, May 20 '15 at 22:04

TimoStaudinger · Accepted Answer · 2015-05-20T22:10:29.243

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)
);

Important note: `ON UPDATE CURRENT_TIMESTAMP` will update ONLY if row's values REALLY changes. So, if you'll update your row with same values then `ON UPDATE CURRENT_TIMESTAMP` value is not changed. For force update the `ON UPDATE CURRENT_TIMESTAMP` value use similar to - http://stackoverflow.com/a/9218768/987850 — 23W, Oct 24 '16 at 10:27

score 0 · Answer 2 · edited Nov 16 '16 at 18:31

0

Sorry can't comment without enough reputation, and I don't know what is IANA TZ Database

But you can try to add On update CURRENT_TIMESTAMP when you create the table:

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

edited Nov 16 '16 at 18:31

Zoltan Toth

46,981
12
120
134

answered May 20 '15 at 22:16

Jaaaaaaay

1,895
2
15
24

score 0 · Answer 3 · answered Jun 27 '20 at 16:16

0

it is quite simple

update table set fieldname=now() where fieldname = value;

here we assume that field we try to update is timestamp type field

answered Jun 27 '20 at 16:16

The EasyLearn Academy

797
11
22

Force MySQL to update TIMESTAMP column

3 Answers3