Unfortunately, MySQL does yet not support table definitions with two different automatic timestamps. There are a few ways around this limitation.
One solution is to forego attempts at automatic timestamps, and explicitly set the time in the data manipulation statements, using the NOW() or CURRENT_DATE() functions:
INSERT INTO table1 (someData, create_time, update_time) VALUES ('someValue', NOW(), NOW()); UPDATE table1 SET someData = 'someOtherValue', update_time = NOW();
A closer step to automation is to define only the update_time as a timestamp, and explicitly set the time during creation:
INSERT INTO table1 (someData, create_time) VALUES ('someValue', NOW()); UPDATE table1 SET someData = 'someOtherValue';
In versions 4.0 and older, the first timestamp field in a table (and only that one!) would automatically update unless given some other value. Starting in version 4.1, you have a great deal more control over which field automatically updates and when.
The closest we can get with MySQL is to build a trigger on the table to automatically populate the fields:
CREATE TRIGGER table1_BI BEFORE INSERT ON table1 FOR EACH ROW NEW.create_time := NOW(), NEW.update_time := NOW(); INSERT INTO table1 (someData) VALUES ('someValue'); UPDATE table1 SET someData = 'someOtherValue';
This was first published in July 2006