MySQL Cookbook, 2nd Edition

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 (Choosing a Temporal Data Type) and the conversion of TIMESTAMP values to and from UTC when they are stored and retrieved (Setting the Client Time Zone). 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 ...