MySQL Cookbook, 2nd Edition

Choosing a Temporal Data Type

Problem

You need to store temporal data but aren’t sure which is the most appropriate data type.

Solution

Choose the data type according to the characteristics of the information to be stored and how you need to use it. Consider questions such as these:

• Do you need times only, dates only, or combined date and time values?

• What range of values do you require?

• Do you want automatic initialization of the column to the current date and time?

Discussion

MySQL provides DATE and TIME data types for representing date and time values separately, and DATETIME and TIMESTAMP types for combined date-and-time values. These values have the following characteristics:

• DATE values are handled as strings inCCYY-MM-DD format, where CC, YY, MM, and DD represent the century, year within century, month, and day parts of the date. The supported range for DATE values is 1000-01-01 to 9999-12-31.

• TIME values are represented as strings inhh:mm:ss format, where hh, mm, and ss are the hours, minutes, and seconds parts of the time. TIME values often can be thought of as time-of-day values, but MySQL actually treats them as elapsed time. Thus, they may be greater than 23:59:59 or even negative. (The actual range of a TIME column is -838:59:59 to 838:59:59.)

• DATETIME and TIMESTAMP values are represented as combined date-and-time strings in CCYY-MM-DDhh:mm:ss format. (Before MySQL 4.1, TIMESTAMP display format was CCYYMMDDhhmmss numeric format. Older applications that depend on this display ...