Problem
You need to store temporal data from MySQL Database 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.
To choose a temporal data type, 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?
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 have CCYY-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 have hh: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 aTIME
column is -838:59:59 to 838:59:59.)
DATETIME
andTIMESTAMP
are combined date-and-time values in CCYY-MM-DD hh:mm:ss format.The
DATETIME
andTIMESTAMP
data types are similar in many respects, but watch out for these differences:DATETIME
has a supported range of 1000-01-01 00:00:00 to 9999-12-31- 23:59:59, whereas
TIMESTAMP
values are valid only from the year 1970 partially through 2038. TIMESTAMP
andDATETIME
have specialauto-initialization
andauto-update
properties, but forDATETIME
they are not available before MySQL 5.6.5.- When a client inserts a
TIMESTAMP
value, the server converts it from the time zone associated with the client session to UTC and stores the UTC value. When the client retrieves aTIMESTAMP
value, the server performs the reverse operation to convert the UTC value back to the client session time zone. A client in a time zone different from the server can configure its session so that this conversion is appropriate for its own time zone.
- Types that include a time part can have a fractional seconds part for subsecond resolution.