Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from 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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata