Using TIMESTAMP Values

5.32.1 Problem

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

5.32.2 Solution

The TIMESTAMP column type can be used for this. However, it has properties that sometimes surprise people, so read this section to make sure you know what you'll be getting. Then read the next few sections for some applications of TIMESTAMP columns.

5.32.3 Discussion

MySQL supports a TIMESTAMP column type that in many ways can be treated the same way as the DATETIME type. However, the TIMESTAMP type has some special properties:

  • The first TIMESTAMP column in a table is special at record-creation time: its default value is the current date and time. This means you need not specify its value at all in an INSERT statement if you want the column set to the record's creation time; MySQL will initialize it automatically. This also occurs if you set the column to NULL when creating the record.
  • The first TIMESTAMP is also special whenever any columns in a row are changed from their current values. MySQL automatically updates its value to the date and time at which the change was made. Note that the update happens only if you actually change a column value. Setting a column to its current value doesn't update the TIMESTAMP.
  • Other TIMESTAMP columns in a table are not special in the same way as the first one. Their default value is 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 set to the current date and time at any time by setting it to NULL. This is true for any TIMESTAMP column, not just the first one.

The TIMESTAMP properties that relate to record creation and modification make this column type particularly suited for certain kinds of problems, such as automatically recording the times at which table rows are inserted or updated. On the other hand, there are other properties that can be somewhat limiting:

  • TIMESTAMP values are represented in CCYYMMDDhhmmss format, which isn't especially intuitive or easy to read, and often needs reformatting for display.
  • The range for TIMESTAMP values starts at the beginning of the year 1970 and extends to about 2037. If you need a larger range, you need to use DATETIME values.

The following sections show how to take advantage of the TIMESTAMP type's special properties.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References

MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois © 2008-2020.
If you may any questions please contact us: