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 ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.