MySQL Cookbook, 2nd Edition

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

Using TIMESTAMP to Track Row Modification Times 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 ...