Recipe 16.5. Simulating TIMESTAMP Properties for Other Date and Time Types

Problem

The TIMESTAMP data type provides auto-initialization and auto-update properties. You would like to use these properties for other temporal data types, but the other types allow only constant values for initialization, and they don't auto-update.

Solution

Use an INSERT TRigger to provide the appropriate current date or time value at record creation time. Use an UPDATE trigger to update the column to the current date or time when the row is changed.

Discussion

Section 6.5 describes the special initialization and update properties of the TIMESTAMP data type that enable you to record row creation and modification times automatically. These properties are not available for other temporal types, although there are reasons you might like them to be. For example, if you use separate DATE and TIME columns to store record-modification times, you can index the DATE column to enable efficient date-based lookups. (With TIMESTAMP, you cannot index just the date part of the column.)

One way to simulate TIMESTAMP properties for other temporal data types is to use the following strategy:

• When you create a row, initialize a DATE column to the current date and a TIME column to the current time.

• When you update a row, set the DATE and TIME columns to the new date and time.

However, this strategy requires all applications that use the table to implement the same strategy, and it fails if even one application neglects to do so. To place the burden of remembering to set the columns properly on the MySQL server and not on application writers, use triggers for the table. This is, in fact, a particular application of the general strategy discussed in Section 16.4 that uses triggers to provide calculated values for initializing (or updating) row columns.

The following example shows how to use triggers to simulate TIMESTAMP properties for each of the DATE, TIME, and DATETIME data types. Begin by creating the following table, which has a nontemporal column for storing data and columns for the DATE, TIME, and DATETIME temporal types:

CREATE TABLE ts_emulate (   data CHAR(10),   d    DATE,   t    TIME,   dt   DATETIME ); 

The intent here is that applications will insert or update values in the data column, and MySQL should set the temporal columns appropriately to reflect the time at which modifications occur. To accomplish this, set up triggers that use the current date and time to initialize the temporal columns for new rows, and to update them when existing rows are changed. A BEFORE INSERT trigger handles new row creation by invoking the CURDATE⁠(⁠ ⁠ ⁠), CURTIME⁠(⁠ ⁠ ⁠), and NOW⁠(⁠ ⁠ ⁠) functions to get the current date, time, and date-and-time values and using those values to set the temporal columns:

CREATE TRIGGER bi_ts_emulate BEFORE INSERT ON ts_emulate FOR EACH ROW SET NEW.d = CURDATE(), NEW.t = CURTIME(), NEW.dt = NOW(); 

A BEFORE UPDATE TRigger handles updates to the temporal columns when the data column changes value. An IF statement is required here to emulate the TIMESTAMP property that an update occurs only if the values in the row actually change from their current values:

CREATE TRIGGER bu_ts_emulate BEFORE UPDATE ON ts_emulate FOR EACH ROW BEGIN   # update temporal columns only if the nontemporal column changes   IF NEW.data <> OLD.data THEN     SET NEW.d = CURDATE(), NEW.t = CURTIME(), NEW.dt = NOW();   END IF; END; 

To test the INSERT trigger, create a couple of rows, but supply a value only for the data column. Then verify that MySQL provides the proper default values for the temporal columns:

mysql> INSERT INTO ts_emulate (data) VALUES('cat'); mysql> INSERT INTO ts_emulate (data) VALUES('dog'); mysql> SELECT * FROM ts_emulate; +------+------------+----------+---------------------+ | data | d          | t        | dt                  | +------+------------+----------+---------------------+ | cat  | 2006-06-23 | 13:29:44 | 2006-06-23 13:29:44 | | dog  | 2006-06-23 | 13:29:49 | 2006-06-23 13:29:49 | +------+------------+----------+---------------------+ 

Change the data value of one row to verify that the BEFORE UPDATE TRigger updates the temporal columns of the changed row:

mysql> UPDATE ts_emulate SET data = 'axolotl' WHERE data = 'cat'; mysql> SELECT * FROM ts_emulate; +---------+------------+----------+---------------------+ | data    | d          | t        | dt                  | +---------+------------+----------+---------------------+ | axolotl | 2006-06-23 | 13:30:12 | 2006-06-23 13:30:12 | | dog     | 2006-06-23 | 13:29:49 | 2006-06-23 13:29:49 | +---------+------------+----------+---------------------+ 

Issue another UPDATE, but this time use one that does not change any data column values. In this case, the BEFORE UPDATE trigger should notice that no value change occurred and leave the temporal columns unchanged:

mysql> UPDATE ts_emulate SET data = data; mysql> SELECT * FROM ts_emulate; +---------+------------+----------+---------------------+ | data    | d          | t        | dt                  | +---------+------------+----------+---------------------+ | axolotl | 2006-06-23 | 13:30:12 | 2006-06-23 13:30:12 | | dog     | 2006-06-23 | 13:29:49 | 2006-06-23 13:29:49 | +---------+------------+----------+---------------------+ 

The preceding example shows how to simulate the auto-initialization and auto-update properties offered by TIMESTAMP columns. If you want only one of those properties and not the other, create only one trigger and omit the other.