Recipe 6.5. Using TIMESTAMP to Track Row Modification Times


Problem

You want a row's creation time or last modification time to be recorded automatically.

Solution

Use the TIMESTAMP data type, which has auto-initialization and auto-update properties.

Discussion

MySQL supports a TIMESTAMP data type that stores date-and-time values. Earlier sections covered the range of values for TIMESTAMP (Section 6.1) and the conversion of TIMESTAMP values to and from UTC when they are stored and retrieved (Section 6.3). This section focuses on how TIMESTAMP columns enable you to track row creation and update times automatically:

  • One TIMESTAMP column in a table can be treated as special in either or both of the following ways:

    • The column is automatically initialized to the current date and time when new rows are created. This means you need not specify its value at all in an INSERT statement; MySQL initializes it automatically to the row's creation time. (This also occurs if you set the column to NULL.)

    • The column is automatically updated to the current date and time when you change any other column in the row from its current value. The update happens only if you actually change a column value; setting a column to its current value doesn't update the TIMESTAMP.

      This auto-update property sometimes surprises people who don't realize that changing another column also updates the TIMESTAMP column. This will never surprise you, of course, because you're aware of it!

  • There can be multiple TIMESTAMP columns in a table, but only one of them can have the special properties just described. Other TIMESTAMP columns have a default value of zero, not the current date and time. Also, their value does not change automatically when you modify other columns; to update them, you must change them yourself.

  • A TIMESTAMP column can be updated to the current date and time at any time by setting it to NULL, unless it has specifically been defined to allow NULL values. This is true for any TIMESTAMP column, not just the first one.

The special properties that relate to row creation and modification make the TIMESTAMP data type particularly suited for certain kinds of problems, such as automatically recording the times at which table rows are inserted or updated. The following discussion shows how to take advantage of these properties.

The syntax for defining TIMESTAMP columns is covered in full in the MySQL Reference Manual. Here we cover only some simple cases. By default, if you specify no special handling for the first TIMESTAMP column in a table, it is equivalent to specifying that it should have both the auto-initialize and auto-update properties explicitly. You can see this as follows, where the SHOW CREATE TABLE statement displays the full TIMESTAMP definition that results from the CREATE TABLE statement:

mysql> CREATE TABLE t (ts TIMESTAMP); mysql> SHOW CREATE TABLE t\G *************************** 1. row ***************************        Table: t Create Table: CREATE TABLE `t` (   `ts` timestamp NOT NULL   DEFAULT CURRENT_TIMESTAMP   ON UPDATE CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 

If you explicitly specify only the DEFAULT or ON UPDATE attribute for the TIMESTAMP column, it has only that attribute and not the other one.

The NOT NULL shown in the column definition might seem a curious thing, given that you can insert NULL into the column, and no error occurs. What this means is that, although you can specify NULL as the value to be inserted, you cannot store NULL because MySQL stores the current date and time instead.

To create a table in which each row contains a value that indicates when the row was created or most recently updated, include a TIMESTAMP column. MySQL will set the column to the current date and time when you create a new row, and update the column whenever you update the value of another column in the row. Suppose that you create a table tsdemo1 with a TIMESTAMP as one of its columns:

CREATE TABLE tsdemo1 (   ts  TIMESTAMP,   val INT ); 

In this case, the ts column has both the auto-initialize and auto-update properties, for reasons just discussed. Insert a couple of rows into the table, and then select its contents. (Issue the INSERT statements a few seconds apart so that the timestamps differ.) The first INSERT statement shows that you can set ts to the current date and time by omitting it from the INSERT statement entirely; the second shows that you can do so by setting ts explicitly to NULL:

mysql> INSERT INTO tsdemo1 (val) VALUES(5); mysql> INSERT INTO tsdemo1 (ts,val) VALUES(NULL,10); mysql> SELECT * FROM tsdemo1; +---------------------+------+ | ts                  | val  | +---------------------+------+ | 2006-06-03 08:21:26 |    5 | | 2006-06-03 08:21:31 |   10 | +---------------------+------+ 

Now issue a statement that changes one row'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; +---------------------+------+ | ts                  | val  | +---------------------+------+ | 2006-06-03 08:21:52 |    6 | | 2006-06-03 08:21:31 |   10 | +---------------------+------+ 

The result shows that the TIMESTAMP column of the modified row was updated.

If you modify multiple rows, the TIMESTAMP value in each of them is updated:

mysql> UPDATE tsdemo1 SET val = val + 1; mysql> SELECT * FROM tsdemo1; +---------------------+------+ | ts                  | val  | +---------------------+------+ | 2006-06-03 08:22:00 |    7 | | 2006-06-03 08:22:00 |   11 | +---------------------+------+ 

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

mysql> UPDATE tsdemo1 SET val = val; mysql> SELECT * FROM tsdemo1; +---------------------+------+ | ts                  | val  | +---------------------+------+ | 2006-06-03 08:22:00 |    7 | | 2006-06-03 08:22:00 |   11 | +---------------------+------+ 

If you want the TIMESTAMP column to be set initially to the time at which a row is created, but to remain constant thereafter, define it to auto-initialize but not auto-update. To do this, the TIMESTAMP definition can be given as shown in the following table:

CREATE TABLE tsdemo2 (   t_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP,   val      INT ); 

Create the table, and then insert records into it with the TIMESTAMP column not specified (or specified as NULL) to initialize it to the current date and time:

mysql> INSERT INTO tsdemo2 (val) VALUES(5); mysql> INSERT INTO tsdemo2 (t_create,val) VALUES(NULL,10); mysql> SELECT * FROM tsdemo2; +---------------------+------+ | t_create            | val  | +---------------------+------+ | 2006-06-03 08:26:00 |    5 | | 2006-06-03 08:26:05 |   10 | +---------------------+------+ 

After inserting the records, change the val column, and then verify that the update leaves the t_create column unchanged (that is, set to the record-creation time):

mysql> UPDATE tsdemo2 SET val = val + 1; mysql> SELECT * FROM tsdemo2; +---------------------+------+ | t_create            | val  | +---------------------+------+ | 2006-06-03 08:26:00 |    6 | | 2006-06-03 08:26:05 |   11 | +---------------------+------+ 

See Also

If you want to simulate the TIMESTAMP auto-initialization and auto-update properties for other temporal types, you can use triggers (Section 16.5).




MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2004
Pages: 375
Authors: Paul DuBois

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net