You need to store temporal data from MySQL Database but aren’t sure which is the most appropriate data type.
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?
TIME data types for representing date and time values separately, and
TIMESTAMP types for combined date-and-time values. These values have the following characteristics:
DATEvalues 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.
TIMEvalues have hh:mm:ss format, where hh, mm, and ss are the hours, minutes, and seconds parts of the time.
TIMEvalues 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
TIMEcolumn is -838:59:59 to 838:59:59.)
TIMESTAMPare combined date-and-time values in CCYY-MM-DD hh:mm:ss format.
TIMESTAMPdata types are similar in many respects, but watch out for these differences:
DATETIMEhas a supported range of 1000-01-01 00:00:00 to 9999-12-31
- 23:59:59, whereas
TIMESTAMPvalues are valid only from the year 1970 partially through 2038.
auto-updateproperties, but for
DATETIMEthey are not available before MySQL 5.6.5.
- When a client inserts a
TIMESTAMPvalue, the server converts it from the time zone associated with the client session to UTC and stores the UTC value. When the client retrieves a
TIMESTAMPvalue, 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.