MySQL create time and update time timestamp

Question

I am creating some tables where I want to store the time when a record was created and when it was last updated. I thought I could have two timestamp fields where one would have the value CURRENT_TIMESTAMP and the other would have CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. But I guess I can't do this because you can have only 1 timestamp field with a default value in a table?

How would you recommend I get and store the two times? Thanks!

Answer 1

A good way to create fields like 'created' and 'updated' is

CREATE TABLE `mytable` ( 
`id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, 
`created` TIMESTAMP DEFAULT '0000-00-00 00:00:00', 
`updated` TIMESTAMP DEFAULT now() ON UPDATE now(),
`myfield` VARCHAR(255)
); 

And its necessary to enter nulls into both columns during "insert":

INSERT INTO mytable (created,updated,myfield) VALUES (null,null,'blablabla');

And now, in all updates, the 'updated' field will have a new value with actual date.

UPDATE mytable SET myfield='blablablablu' WHERE myfield='blablabla';

Source : http://gusiev.com/2009/04/update-and-create-timestamps-with-mysql