This Content Component encountered an error
Problem solve Get help with specific problems with your technologies, process and projects.

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:

NEW.create_time := NOW(), NEW.update_time := NOW();
INSERT INTO table1 (someData) VALUES ('someValue');
UPDATE table1 SET someData = 'someOtherValue';
This was last 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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.