Ask the Expert

Timestamping fields in tables

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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: