MySQL Cookbook

Recording a Row’s Last Modification Time

Problem

You want to automatically record the time when a record was last updated.

Solution

Include a TIMESTAMP column in your table.

Discussion

To create a table where each row contains a value that indicates when the record was most recently updated, include a TIMESTAMP column. The column will be set to the current date and time when you create a new row, and updated whenever you update the value of another column in the row. Suppose you create a table tsdemo1 with a TIMESTAMP column that looks like this:

CREATE TABLE tsdemo1
(
    t   TIMESTAMP,
    val INT
);

Insert a couple of records into the table and then select its contents. (Issue the INSERT queries a few seconds apart so that you can see how the timestamps differ.) The first INSERT statement shows that you can set t to the current date and time by setting it explicitly to NULL; the second shows that you set t by omitting it from the INSERT statement entirely:

mysql> INSERT INTO tsdemo1 (t,val) VALUES(NULL,5);
mysql> INSERT INTO tsdemo1 (val) VALUES(10);
mysql> SELECT * FROM tsdemo1;
+----------------+------+
| t              | val  |
+----------------+------+
| 20020715115825 |    5 |
| 20020715115831 |   10 |
+----------------+------+

Now issue a query that changes one record’s val column and check its effect on the table’s contents:

mysql> UPDATE tsdemo1 SET val = 6 WHERE val = 5;
mysql> SELECT * FROM tsdemo1; +----------------+------+ | t | val | +----------------+------+ | 20020715115915 | 6 | | 20020715115831 ...