How do you set a table to timestamp one field on insert and another on update?

    Requires Free Membership to View

    Login

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top