Recording a Rows Last Modification Time

Recording a Row s Last Modification Time

5.33.1 Problem

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

5.33.2 Solution

Include a TIMESTAMP column in your table.

5.33.3 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 | 10 |
+----------------+------+

The result shows that the TIMESTAMP has been updated only for the modified record.

If you modify multiple records, the TIMESTAMP values in all of them will be updated:

mysql> UPDATE tsdemo1 SET val = val + 1;
mysql> SELECT * FROM tsdemo1;
+----------------+------+
| t | val |
+----------------+------+
| 20020715115926 | 7 |
| 20020715115926 | 11 |
+----------------+------+

Issuing an UPDATE statement that doesn't actually change the values in the val column doesn't update the TIMESTAMP values. To see this, set every record's val column to its current value, then review the contents of the table:

mysql> UPDATE tsdemo1 SET val = val + 0;
mysql> SELECT * FROM tsdemo1;
+----------------+------+
| t | val |
+----------------+------+
| 20020715115926 | 7 |
| 20020715115926 | 11 |
+----------------+------+

An alternative to using a TIMESTAMP is to use a DATETIME column and set it to NOW( ) explicitly when you create a record and whenever you update a record. However, in this case, all applications that use the table must implement the same strategy, which fails if even one application neglects to do so.