A summary of the temporal data types follows. For additional information about properties of the temporal types, see Section 10.3, “Date and Time Types”. Storage requirements are given in Section 10.5, “Data Type Storage Requirements”. Functions that operate on temporal values are described at Section 11.7, “Date and Time Functions”.
For the DATETIME and
DATE range descriptions,
“supported” means that although earlier values
might work, there is no guarantee.
A date. The supported range is
'1000-01-01'to'9999-12-31'. MySQL displaysDATEvalues in'YYYY-MM-DD'format, but permits assignment of values toDATEcolumns using either strings or numbers.A date and time combination. The supported range is
'1000-01-01 00:00:00'to'9999-12-31 23:59:59'. MySQL displaysDATETIMEvalues in'YYYY-MM-DD HH:MM:SS'format, but permits assignment of values toDATETIMEcolumns using either strings or numbers.A timestamp. The range is
'1970-01-01 00:00:01'UTC to'2038-01-19 03:14:07'UTC.TIMESTAMPvalues are stored as the number of seconds since the epoch ('1970-01-01 00:00:00'UTC). ATIMESTAMPcannot represent the value'1970-01-01 00:00:00'because that is equivalent to 0 seconds from the epoch and the value 0 is reserved for representing'0000-00-00 00:00:00', the “zero”TIMESTAMPvalue.A
TIMESTAMPcolumn is useful for recording the date and time of anINSERTorUPDATEoperation. By default, the firstTIMESTAMPcolumn in a table is automatically set to the date and time of the most recent operation if you do not assign it a value yourself. You can also set anyTIMESTAMPcolumn to the current date and time by assigning it aNULLvalue. Variations on automatic initialization and update properties are described in Section 10.3.1.1, “TIMESTAMPProperties”.A
TIMESTAMPvalue is returned as a string in the format'YYYY-MM-DD HH:MM:SS'with a display width fixed at 19 characters. To obtain the value as a number, you should add+0to the timestamp column.NoteThe
TIMESTAMPformat that was used prior to MySQL 4.1 is not supported in MySQL 5.5; see MySQL 3.23, 4.0, 4.1 Reference Manual for information regarding the old format.A time. The range is
'-838:59:59'to'838:59:59'. MySQL displaysTIMEvalues in'HH:MM:SS'format, but permits assignment of values toTIMEcolumns using either strings or numbers.A year in two-digit or four-digit format. The default is four-digit format. In four-digit format, the permissible values are
1901to2155, and0000. In two-digit format, the permissible values are70to69, representing years from 1970 to 2069. MySQL displaysYEARvalues inYYYYformat, but permits assignment of values toYEARcolumns using either strings or numbers.
The SUM() and
AVG() aggregate functions do not
work with temporal values. (They convert the values to numbers,
which loses the part after the first nonnumeric character.) To
work around this problem, you can convert to numeric units,
perform the aggregate operation, and convert back to a temporal
value. Examples:
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROMtbl_name; SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROMtbl_name;