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 displaysDATE
values in'YYYY-MM-DD'
format, but permits assignment of values toDATE
columns 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 displaysDATETIME
values in'YYYY-MM-DD HH:MM:SS'
format, but permits assignment of values toDATETIME
columns 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.TIMESTAMP
values are stored as the number of seconds since the epoch ('1970-01-01 00:00:00'
UTC). ATIMESTAMP
cannot 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”TIMESTAMP
value.A
TIMESTAMP
column is useful for recording the date and time of anINSERT
orUPDATE
operation. By default, the firstTIMESTAMP
column 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 anyTIMESTAMP
column to the current date and time by assigning it aNULL
value. Variations on automatic initialization and update properties are described in Section 10.3.1.1, “TIMESTAMP
Properties”.A
TIMESTAMP
value 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+0
to the timestamp column.NoteThe
TIMESTAMP
format 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 displaysTIME
values in'HH:MM:SS'
format, but permits assignment of values toTIME
columns 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
1901
to2155
, and0000
. In two-digit format, the permissible values are70
to69
, representing years from 1970 to 2069. MySQL displaysYEAR
values inYYYY
format, but permits assignment of values toYEAR
columns 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
;