13.2.2 DATE、DATETIME 和 TIMESTAMP 类型
The DATE
, DATETIME
, and TIMESTAMP
types are related. This section describes their characteristics, how they are similar, and how they differ. MySQL recognizes DATE
, DATETIME
, and TIMESTAMP
values in several formats, described in Section 11.1.3, “Date and Time Literals”. For the DATE
and DATETIME
range descriptions, “supported” means that although earlier values might work, there is no guarantee.
The DATE
type is used for values with a date part but no time part. MySQL retrieves and displays DATE
values in '
format. The supported range is YYYY-MM-DD
''1000-01-01'
to '9999-12-31'
.
The DATETIME
type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME
values in '
format. The supported range is YYYY-MM-DD hh:mm:ss
''1000-01-01 00:00:00'
to '9999-12-31 23:59:59'
.
The TIMESTAMP
data type is used for values that contain both date and time parts. TIMESTAMP
has a range of '1970-01-01 00:00:01'
UTC to '2038-01-19 03:14:07'
UTC.
A DATETIME
or TIMESTAMP
value can include a trailing fractional seconds part in up to microseconds (6 digits) precision. In particular, any fractional part in a value inserted into a DATETIME
or TIMESTAMP
column is stored rather than discarded. With the fractional part included, the format for these values is '
, the range for YYYY-MM-DD hh:mm:ss
[.fraction
]'DATETIME
values is '1000-01-01 00:00:00.000000'
to '9999-12-31 23:59:59.499999'
, and the range for TIMESTAMP
values is '1970-01-01 00:00:01.000000'
to '2038-01-19 03:14:07.499999'
. The fractional part should always be separated from the rest of the time by a decimal point; no other fractional seconds delimiter is recognized. For information about fractional seconds support in MySQL, see Section 13.2.6, “Fractional Seconds in Time Values”.
The TIMESTAMP
and DATETIME
data types offer automatic initialization and updating to the current date and time. For more information, see Section 13.2.5, “Automatic Initialization and Updating for TIMESTAMP and DATETIME”.
MySQL converts TIMESTAMP
values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME
.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP
value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored. This occurs because the same time zone was not used for conversion in both directions. The current time zone is available as the value of the time_zone
system variable. For more information, see Section 7.1.15, “MySQL Server Time Zone Support”.
You can specify a time zone offset when inserting a TIMESTAMP
or DATETIME
value into a table. See Section 11.1.3, “Date and Time Literals”, for more information and examples.
Invalid DATE
, DATETIME
, or TIMESTAMP
values are converted to the “zero” value of the appropriate type ('0000-00-00'
or '0000-00-00 00:00:00'
), if the SQL mode permits this conversion. The precise behavior depends on which if any of strict SQL mode and the NO_ZERO_DATE
SQL mode are enabled; see Section 7.1.11, “Server SQL Modes”.
You can convert TIMESTAMP
values to UTC DATETIME
values when retrieving them using CAST()
with the AT TIME ZONE
operator, as shown here:
mysql> SELECT col,
> CAST(col AT TIME ZONE INTERVAL '+00:00' AS DATETIME) AS ut
> FROM ts ORDER BY id;
+---------------------+---------------------+
| col | ut |
+---------------------+---------------------+
| 2020-01-01 10:10:10 | 2020-01-01 15:10:10 |
| 2019-12-31 23:40:10 | 2020-01-01 04:40:10 |
| 2020-01-01 13:10:10 | 2020-01-01 18:10:10 |
| 2020-01-01 10:10:10 | 2020-01-01 15:10:10 |
| 2020-01-01 04:40:10 | 2020-01-01 09:40:10 |
| 2020-01-01 18:10:10 | 2020-01-01 23:10:10 |
+---------------------+---------------------+
For complete information regarding syntax and additional examples, see the description of the CAST()
function.
Be aware of certain properties of date value interpretation in MySQL:
-
MySQL permits a “relaxed” format for values specified as strings, in which any punctuation character may be used as the delimiter between date parts or time parts. In some cases, this syntax can be deceiving. For example, a value such as
'10:11:12'
might look like a time value because of the:
, but is interpreted as the year'2010-11-12'
if used in date context. The value'10:45:15'
is converted to'0000-00-00'
because'45'
is not a valid month.The only delimiter recognized between a date and time part and a fractional seconds part is the decimal point.
-
The server requires that month and day values be valid, and not merely in the range 1 to 12 and 1 to 31, respectively. With strict mode disabled, invalid dates such as
'2004-04-31'
are converted to'0000-00-00'
and a warning is generated. With strict mode enabled, invalid dates generate an error. To permit such dates, enableALLOW_INVALID_DATES
. See Section 7.1.11, “Server SQL Modes”, for more information. -
MySQL does not accept
TIMESTAMP
values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special “zero” value'0000-00-00 00:00:00'
, if the SQL mode permits this value. The precise behavior depends on which if any of strict SQL mode and theNO_ZERO_DATE
SQL mode are enabled; see Section 7.1.11, “Server SQL Modes”. -
Dates containing 2-digit year values are ambiguous because the century is unknown. MySQL interprets 2-digit year values using these rules:
-
Year values in the range
00-69
become2000-2069
. -
Year values in the range
70-99
become1970-1999
.
See also Section 13.2.9, “2-Digit Years in Dates”.
-