A summary of the temporal data types follows. For additional information about properties and storage requirements of the temporal types, see Section 11.3, “Date and Time Types”, and Section 11.7, “Data Type Storage Requirements”. For descriptions of functions that operate on temporal values, see Section 12.7, “Date and Time Functions”.
A date and time combination. The supported range is
'9999-12-31 23:59:59'. MySQL displays
'YYYY-MM-DD hh:mm:ss'format, but permits assignment of values to
DATETIMEcolumns using either strings or numbers.
A timestamp. The range is
'1970-01-01 00:00:01'UTC to
TIMESTAMPvalues are stored as the number of seconds since the epoch (
'1970-01-01 00:00:00'UTC). A
TIMESTAMPcannot 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”
Unless specified otherwise, the first
TIMESTAMPcolumn in a table is defined to be automatically set to the date and time of the most recent modification if not explicitly assigned a value. This makes
TIMESTAMPuseful for recording the timestamp of an
UPDATEoperation. You can also set any
TIMESTAMPcolumn to the current date and time by assigning it a
NULLvalue, unless it has been defined with the
NULLattribute to permit
NULLvalues. The automatic initialization and updating to the current date and time can be specified using
ON UPDATE CURRENT_TIMESTAMPclauses, as described in Section 11.3.5, “Automatic Initialization and Updating for TIMESTAMP”.
A year in 2-digit or 4-digit format. The default is 4-digit format.
YEAR(4)differ in display format, but have the same range of values. In 4-digit format, values display as
0000. In 2-digit format, values display as
69, representing years from 1970 to 2069. MySQL displays
YYformat, but permits assignment of values to
YEARcolumns using either strings or numbers.Note
YEAR(2)data type has certain issues that you should consider before choosing to use it. As of MySQL 5.5.27,
YEAR(2)is deprecated. For more information, see Section 11.3.4, “2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR”.
For additional information about
YEARdisplay format and interpretation of input values, see Section 11.3.3, “The YEAR Type”.
AVG() aggregate functions do not
work with temporal values. (They convert the values to numbers,
losing everything after the first nonnumeric character.) To work
around this problem, convert to numeric units, perform the
aggregate operation, and convert back to a temporal value.
SELECT SEC_TO_TIME(SUM(TIME_TO_SEC(time_col))) FROM tbl_name; SELECT FROM_DAYS(SUM(TO_DAYS(date_col))) FROM tbl_name;
The MySQL server can be run with the
MAXDB SQL mode enabled. In
this mode is enabled at the time that a table is created,
TIMESTAMP columns are created
DATETIME columns. As a
result, such columns use
DATETIME display format, have
the same range of values, and there is no automatic
initialization or updating to the current date and time. See
Section 5.1.10, “Server SQL Modes”.