11.2.1 Date and Time Data Type Syntax
The date and time data types for representing temporal values are DATE
, TIME
, DATETIME
, TIMESTAMP
, and YEAR
.
For the DATE
and DATETIME
range descriptions, “supported” means that although earlier values might work, there is no guarantee.
MySQL permits fractional seconds for TIME
, DATETIME
, and TIMESTAMP
values, with up to microseconds (6 digits) precision. To define a column that includes a fractional seconds part, use the syntax
, where type_name
(fsp
)type_name
is TIME
, DATETIME
, or TIMESTAMP
, and fsp
is the fractional seconds precision. For example:
CREATE TABLE t1 (t TIME(3), dt DATETIME(6), ts TIMESTAMP(0));
The fsp
value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)
Any TIMESTAMP
or DATETIME
column in a table can have automatic initialization and updating properties; see Section 11.2.6, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.
A date. The supported range is
'1000-01-01'
to'9999-12-31'
. MySQL displaysDATE
values in'
format, but permits assignment of values toYYYY-MM-DD
'DATE
columns using either strings or numbers.A date and time combination. The supported range is
'1000-01-01 00:00:00.000000'
to'9999-12-31 23:59:59.999999'
. MySQL displaysDATETIME
values in'
format, but permits assignment of values toYYYY-MM-DD hh:mm:ss
[.fraction
]'DATETIME
columns using either strings or numbers.An optional
fsp
value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.Automatic initialization and updating to the current date and time for
DATETIME
columns can be specified usingDEFAULT
andON UPDATE
column definition clauses, as described in Section 11.2.6, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.A timestamp. The range is
'1970-01-01 00:00:01.000000'
UTC to'2038-01-19 03:14:07.999999'
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.An optional
fsp
value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.The way the server handles
TIMESTAMP
definitions depends on the value of theexplicit_defaults_for_timestamp
system variable (see Section 5.1.7, “Server System Variables”).If
explicit_defaults_for_timestamp
is enabled, there is no automatic assignment of theDEFAULT CURRENT_TIMESTAMP
orON UPDATE CURRENT_TIMESTAMP
attributes to anyTIMESTAMP
column. They must be included explicitly in the column definition. Also, anyTIMESTAMP
not explicitly declared asNOT NULL
permitsNULL
values.If
explicit_defaults_for_timestamp
is disabled, the server handlesTIMESTAMP
as follows:Unless specified otherwise, the first
TIMESTAMP
column 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 makesTIMESTAMP
useful for recording the timestamp of anINSERT
orUPDATE
operation. You can also set anyTIMESTAMP
column to the current date and time by assigning it aNULL
value, unless it has been defined with theNULL
attribute to permitNULL
values.Automatic initialization and updating to the current date and time can be specified using
DEFAULT CURRENT_TIMESTAMP
andON UPDATE CURRENT_TIMESTAMP
column definition clauses. By default, the firstTIMESTAMP
column has these properties, as previously noted. However, anyTIMESTAMP
column in a table can be defined to have these properties.A time. The range is
'-838:59:59.000000'
to'838:59:59.000000'
. MySQL displaysTIME
values in'
format, but permits assignment of values tohh:mm:ss
[.fraction
]'TIME
columns using either strings or numbers.An optional
fsp
value in the range from 0 to 6 may be given to specify fractional seconds precision. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0.A year in 4-digit format. MySQL displays
YEAR
values inYYYY
format, but permits assignment of values toYEAR
columns using either strings or numbers. Values display as1901
to2155
, or0000
.NoteThe
YEAR(2)
data type is deprecated and support for it is removed in MySQL 5.7.5. To convert 2-digitYEAR(2)
columns to 4-digitYEAR
columns, see Section 11.2.5, “2-Digit YEAR(2) Limitations and Migrating to 4-Digit YEAR”.For additional information about
YEAR
display format and interpretation of input values, see Section 11.2.4, “The YEAR Type”.
The SUM()
and 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. Examples:
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 case, TIMESTAMP
is identical with DATETIME
. If this mode is enabled at the time that a table is created, TIMESTAMP
columns are created as 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”.
As of MySQL 5.7.22, MAXDB
is deprecated. It will be removed in a future version of MySQL.