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 ...