CODEWITHSUNDEEP
mysqltimestampauto-update
user2340426
user2340426

Reputation: 225

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

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?

Upvotes: 1

Views: 6768

Answers (3)

The EasyLearn Academy
The EasyLearn Academy

Reputation: 927

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

Upvotes: 0

Jaaaaaaay
Jaaaaaaay

Reputation: 1975

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

Upvotes: 0

TimoStaudinger
TimoStaudinger

Reputation: 42460

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

Upvotes: 4

Related Questions