Q

Timestamping fields in tables

In this expert response, MySQL expert Scott Noyes offers two workarounds for the lack of automation in the timestamping and updating of fields.

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

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
This Content Component encountered an error

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close