MySQL 8.4 Release Notes
日期和时间值可以用多种格式表示，例如带引号的字符串或数字，这取决于值的具体类型和其他因素。例如，在 MySQL 期望日期的上下文中，它将 '2015-07-21'、'20150721' 和 20150721 解释为日期。Date and time values can be represented in several formats, such
as quoted strings or as numbers, depending on the exact type of
the value and other factors. For example, in contexts where
MySQL expects a date, it interprets any of
'2015-07-21', '20150721',
and 20150721 as a date.
本节描述了日期和时间字面量的可接受格式。有关时间数据类型的更多信息，例如允许值的范围，请参阅第 13.2 节，“日期和时间数据类型”。This section describes the acceptable formats for date and time literals. For more information about the temporal data types, such as the range of permitted values, see Section 13.2, “Date and Time Data Types”.
标准 SQL 要求使用类型关键字和字符串来指定时间字面量。关键字和字符串之间的空格是可选的。Standard SQL requires temporal literals to be specified using a type keyword and a string. The space between the keyword and string is optional.
DATE 'str' TIME 'str' TIMESTAMP 'str'
MySQL 可以识别，但与标准 SQL 不同，它不要求使用类型关键字。为了符合标准的应用程序应为时间字面量包含类型关键字。MySQL recognizes but, unlike standard SQL, does not require the type keyword. Applications that are to be standard-compliant should include the type keyword for temporal literals.
MySQL 还可以识别对应标准 SQL 语法的 ODBC 语法：MySQL also recognizes the ODBC syntax corresponding to the standard SQL syntax:
{ d 'str' }
{ t 'str' }
{ ts 'str' }
MySQL 使用类型关键字和 ODBC 构造来生成 DATE， TIME and DATETIME 值，包括尾部小秒部分（如指定）。TIMESTAMP 语法产生 DATETIME 在 MySQL 中的值，因为 DATETIME其范围更接近标准 SQL TIMESTAMP 类型，其年份范围从 0001 到 9999（MySQL TIMESTAMP 年份范围为 1970 到 2038）MySQL uses the type keywords and the ODBC constructions to
produce DATE,
TIME, and
DATETIME values, respectively,
including a trailing fractional seconds part if specified. The
TIMESTAMP syntax produces a
DATETIME value in MySQL because
DATETIME has a range that more
closely corresponds to the standard SQL
TIMESTAMP type, which has a
year range from 0001 to
9999. (The MySQL
TIMESTAMP year range is
1970 to 2038.)
MySQL 识别这些格式的 DATE 值：MySQL recognizes DATE values in
these formats:
作为字符串，格式可以是
'或YYYY-MM-DD''。允许使用“宽松”语法，但已不推荐：日期部分之间可以使用任何标点符号作为分隔符。例如，YY-MM-DD''2012-12-31'、'2012/12/31'、'2012^12^31'和'2012@12@31'是等效的。使用除短横线 (-) 之外的任何字符作为分隔符会触发警告，如下所示：As a string in either'orYYYY-MM-DD''format. A “relaxed” syntax is permitted, but is deprecated: Any punctuation character may be used as the delimiter between date parts. For example,YY-MM-DD''2012-12-31','2012/12/31','2012^12^31', and'2012@12@31'are equivalent. Using any character other than the dash (-) as the delimiter raises a warning, as shown here:mysql>
SELECT DATE'2012@12@31';+------------------+ | DATE'2012@12@31' | +------------------+ | 2012-12-31 | +------------------+ 1 row in set, 1 warning (0.00 sec) mysql>SHOW WARNINGS\G*************************** 1. row *************************** Level: Warning Code: 4095 Message: Delimiter '@' in position 4 in datetime value '2012@12@31' at row 1 is deprecated. Prefer the standard '-'. 1 row in set (0.00 sec)作为一个字符串，没有分隔符，可以是
'或YYYYMMDD''格式，前提是该字符串能够被理解为日期。例如，YYMMDD''20070523'和'070523'被解释为'2007-05-23'，但'071332'是非法的（它的月份和日期部分不合理），将变为'0000-00-00'。As a string with no delimiters in either'orYYYYMMDD''format, provided that the string makes sense as a date. For example,YYMMDD''20070523'and'070523'are interpreted as'2007-05-23', but'071332'is illegal (it has nonsensical month and day parts) and becomes'0000-00-00'.作为一个数字，可以是
YYYYMMDD或YYMMDD格式，前提是该数字能被理解为日期。例如，19830905和830905被解释为'1983-09-05'。As a number in eitherYYYYMMDDorYYMMDDformat, provided that the number makes sense as a date. For example,19830905and830905are interpreted as'1983-09-05'.
MySQL 识别以下格式的 DATETIME 和 TIMESTAMP 值：MySQL recognizes DATETIME and
TIMESTAMP values in these
formats:
作为字符串，可以是
'YYYY-MM-DD hh:mm:ss'或'YY-MM-DD hh:mm:ss'格式。MySQL 也允许在这里使用 “宽松” 语法，尽管这已经不推荐使用：日期部分或时间部分之间可以使用任何标点字符作为分隔符。例如，'2012-12-31 11:30:45'、'2012^12^31 11+30+45'、'2012/12/31 11*30*45'和'2012@12@31 11^30^45'是等效的。使用除日期部分的短横线（-）和时间部分的冒号（:）以外的任何字符作为分隔符，会引发警告，如下所示：As a string in either'orYYYY-MM-DD hh:mm:ss''format. MySQL also permits a “relaxed” syntax here, although this is deprecated: Any punctuation character may be used as the delimiter between date parts or time parts. For example,YY-MM-DD hh:mm:ss''2012-12-31 11:30:45','2012^12^31 11+30+45','2012/12/31 11*30*45', and'2012@12@31 11^30^45'are equivalent. Use of any characters as delimiters in such values, other than the dash (-) for the date part and the colon (:) for the time part, raises a warning, as shown here:mysql>
SELECT TIMESTAMP'2012^12^31 11*30*45';+--------------------------------+ | TIMESTAMP'2012^12^31 11*30*45' | +--------------------------------+ | 2012-12-31 11:30:45 | +--------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql>SHOW WARNINGS\G*************************** 1. row *************************** Level: Warning Code: 4095 Message: Delimiter '^' in position 4 in datetime value '2012^12^31 11*30*45' at row 1 is deprecated. Prefer the standard '-'. 1 row in set (0.00 sec)日期和时间部分与分数秒部分之间唯一被识别的分隔符是小数点。The only delimiter recognized between a date and time part and a fractional seconds part is the decimal point.
日期和时间部分可以用
T分隔，而不是用空格。例如，'2012-12-31 11:30:45'和'2012-12-31T11:30:45'是等价的。The date and time parts can be separated byTrather than a space. For example,'2012-12-31 11:30:45''2012-12-31T11:30:45'are equivalent.以前，MySQL 支持日期和时间值中任意数量的前导和尾随空白字符，以及
DATETIME和TIMESTAMP值的日期和时间部分之间的空白字符。在 MySQL 8.4 中，这种行为已被弃用，多余的空白字符会触发警告，如下所示：Previously, MySQL supported arbitrary numbers of leading and trailing whitespace characters in date and time values, as well as between the date and time parts ofDATETIMEandTIMESTAMPvalues. In MySQL 8.4, this behavior is deprecated, and the presence of excess whitespace characters triggers a warning, as shown here:mysql>
SELECT TIMESTAMP'2012-12-31 11-30-45';+----------------------------------+ | TIMESTAMP'2012-12-31 11-30-45' | +----------------------------------+ | 2012-12-31 11:30:45 | +----------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql>SHOW WARNINGS\G*************************** 1. row *************************** Level: Warning Code: 4096 Message: Delimiter ' ' in position 11 in datetime value '2012-12-31 11-30-45' at row 1 is superfluous and is deprecated. Please remove. 1 row in set (0.00 sec)当使用除空格字符以外的空白字符时，也会发出警告，例如：A warning is also raised when whitespace characters other than the space character is used, like this:
mysql>
SELECT TIMESTAMP'2021-06-06'>11:15:25';+--------------------------------+ | TIMESTAMP'2021-06-06 11:15:25' | +--------------------------------+ | 2021-06-06 11:15:25 | +--------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql>SHOW WARNINGS\G*************************** 1. row *************************** Level: Warning Code: 4095 Message: Delimiter '\n' in position 10 in datetime value '2021-06-06 11:15:25' at row 1 is deprecated. Prefer the standard ' '. 1 row in set (0.00 sec)每个时间值只会发出一次这样的警告，即使在分隔符、空白字符或两者都存在的情况下存在多个问题，如以下一系列语句所示：Only one such warning is raised per temporal value, even though multiple issues may exist with delimiters, whitespace, or both, as shown in the following series of statements:
mysql>
SELECT TIMESTAMP'2012!-12-31 11:30:45';+----------------------------------+ | TIMESTAMP'2012!-12-31 11:30:45' | +----------------------------------+ | 2012-12-31 11:30:45 | +----------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql>SHOW WARNINGS\G*************************** 1. row *************************** Level: Warning Code: 4095 Message: Delimiter '!' in position 4 in datetime value '2012!-12-31 11:30:45' at row 1 is deprecated. Prefer the standard '-'. 1 row in set (0.00 sec) mysql>SELECT TIMESTAMP'2012-12-31 11:30:45';+---------------------------------+ | TIMESTAMP'2012-12-31 11:30:45' | +---------------------------------+ | 2012-12-31 11:30:45 | +---------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql>SHOW WARNINGS\G*************************** 1. row *************************** Level: Warning Code: 4096 Message: Delimiter ' ' in position 11 in datetime value '2012-12-31 11:30:45' at row 1 is superfluous and is deprecated. Please remove. 1 row in set (0.00 sec) mysql>SELECT TIMESTAMP'2012-12-31 11:30:45';+--------------------------------+ | TIMESTAMP'2012-12-31 11:30:45' | +--------------------------------+ | 2012-12-31 11:30:45 | +--------------------------------+ 1 row in set (0.00 sec)作为一个没有分隔符的字符串，可以采用
'或YYYYMMDDhhmmss''格式，前提是该字符串符合日期的合理性。例如，YYMMDDhhmmss''20070523091528'和'070523091528'被解释为'2007-05-23 09:15:28'，但'071122129015'是非法的（分钟部分不合理），因此会变为'0000-00-00 00:00:00'。As a string with no delimiters in either'orYYYYMMDDhhmmss''format, provided that the string makes sense as a date. For example,YYMMDDhhmmss''20070523091528'and'070523091528'are interpreted as'2007-05-23 09:15:28', but'071122129015'is illegal (it has a nonsensical minute part) and becomes'0000-00-00 00:00:00'.作为一个数字，格式可以是
YYYYMMDDhhmmss或YYMMDDhhmmss，前提是该数字可以被理解为一个日期。例如，19830905132800和830905132800被解释为'1983-09-05 13:28:00'。As a number in eitherYYYYMMDDhhmmssorYYMMDDhhmmssformat, provided that the number makes sense as a date. For example,19830905132800and830905132800are interpreted as'1983-09-05 13:28:00'.
一个 DATETIME 或 TIMESTAMP 值可以包含最多微秒（6 位数字）精度的尾随小数秒部分。小数部分应始终用小数点与时间的其余部分分开；不识别其他小数秒分隔符。有关 MySQL 中小数秒支持的信息，请参见 第 13.2.6 节，“时间值中的小数秒”。A DATETIME or
TIMESTAMP value can include a
trailing fractional seconds part in up to microseconds (6
digits) precision. 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”.
包含两位数年份的日期是不明确的，因为世纪未知。MySQL 使用以下规则解释两位数年份：Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using these rules:
年份值在
70-99范围内时，将变为1970-1999。Year values in the range70-99become1970-1999.年份值在
00-69范围内时，将变为2000-2069。Year values in the range00-69become2000-2069.
另见 第 13.2.9 节，“日期中的两位数年份”。See also Section 13.2.9, “2-Digit Years in Dates”.
对于以字符串形式指定并包含日期部分分隔符的值，如果月份或日期小于 10，不需要指定两位数字。'2015-6-9' 与 '2015-06-09' 相同。同样，对于以字符串形式指定并包含时间部分分隔符的值，如果小时、分钟或秒小于 10，也不需要指定两位数字。'2015-10-30 1:2:3' 与 '2015-10-30 01:02:03' 相同。For values specified as strings that include date part
delimiters, it is unnecessary to specify two digits for month
or day values that are less than 10.
'2015-6-9' is the same as
'2015-06-09'. Similarly, for values
specified as strings that include time part delimiters, it is
unnecessary to specify two digits for hour, minute, or second
values that are less than 10.
'2015-10-30 1:2:3' is the same as
'2015-10-30 01:02:03'.
指定为数字的值应为 6、8、12 或 14 位长。如果数字为 8 或 14 位长，则假定其格式为 YYYYMMDD 或 YYYYMMDDhhmmss，年份由前 4 位数字表示。如果数字为 6 或 12 位长，则假定其格式为 YYMMDD 或 YYMMDDhhmmss，年份由前 2 位数字表示。长度不是这些长度之一的数字，将被解释为通过在前面补零到最近长度进行填充的数字。Values specified as numbers should be 6, 8, 12, or 14 digits
long. If a number is 8 or 14 digits long, it is assumed to be
in YYYYMMDD or
YYYYMMDDhhmmss format and that the
year is given by the first 4 digits. If the number is 6 or 12
digits long, it is assumed to be in
YYMMDD or
YYMMDDhhmmss format and that the
year is given by the first 2 digits. Numbers that are not one
of these lengths are interpreted as though padded with leading
zeros to the closest length.
作为非分隔字符串指定的值将根据其长度进行解释。对于长度为 8 或 14 个字符的字符串，年份假定由前 4 个字符表示。否则，年份假定由前 2 个字符表示。字符串从左到右进行解释以查找年份、月份、日期、小时、分钟和秒的值，解释的部分数量取决于字符串中存在的部分。这意味着你不应该使用少于 6 个字符的字符串。例如，如果你指定 '9903'，认为它表示 1999 年 3 月，MySQL 会将其转换为 “零” 日期值。这是因为年份和月份的值是 99 和 03，但是日期部分完全缺失。然而，你可以显式指定零值来表示缺失的月份或日期部分。例如，要插入值 '1999-03-00'，请使用 '990300'。Values specified as nondelimited strings are interpreted
according their length. For a string 8 or 14 characters long,
the year is assumed to be given by the first 4 characters.
Otherwise, the year is assumed to be given by the first 2
characters. The string is interpreted from left to right to
find year, month, day, hour, minute, and second values, for as
many parts as are present in the string. This means you should
not use strings that have fewer than 6 characters. For
example, if you specify '9903', thinking
that represents March, 1999, MySQL converts it to the
“zero” date value. This occurs because the year
and month values are 99 and
03, but the day part is completely missing.
However, you can explicitly specify a value of zero to
represent missing month or day parts. For example, to insert
the value '1999-03-00', use
'990300'.
MySQL 识别以下格式的 TIME 值：MySQL recognizes TIME values in
these formats:
作为字符串，格式为
'D hh:mm:ss'。你也可以使用以下任意一种“宽松”语法：'hh:mm:ss'、'hh:mm'、'D hh:mm'、'D hh'，或者'ss'。这里D表示天数，其值可以从 0 到 34。As a string in'D hh:mm:ss'format. You can also use one of the following “relaxed” syntaxes:'hh:mm:ss','hh:mm','D hh:mm','D hh', or'ss'. HereDrepresents days and can have a value from 0 to 34.作为没有分隔符的字符串，格式为
'hhmmss'，前提是它符合时间的概念。例如，'101112'被理解为'10:11:12'，但'109712'是非法的（分钟部分不合理），会变成'00:00:00'。As a string with no delimiters in'hhmmss'format, provided that it makes sense as a time. For example,'101112'is understood as'10:11:12', but'109712'is illegal (it has a nonsensical minute part) and becomes'00:00:00'.作为
hhmmss格式的数字，前提是它符合时间的概念。例如，101112被理解为'10:11:12'。以下替代格式也可以被理解：ss、mmss或hhmmss。As a number inhhmmssformat, provided that it makes sense as a time. For example,101112is understood as'10:11:12'. The following alternative formats are also understood:ss,mmss, orhhmmss.
在 'D hh:mm:ss.fraction'、'hh:mm:ss.fraction'、'hhmmss.fraction' 和 hhmmss.fraction 时间格式中可以识别尾随的秒的小数部分，其中 fraction 是精确到微秒（最多 6 位数字）的秒的小数部分。小数部分应始终通过小数点与时间的其余部分分开；其他小数秒分隔符不被识别。有关 MySQL 中对秒的小数支持的信息，请参见 第 13.2.6 节，“时间值中的小数秒”。A trailing fractional seconds part is recognized in the
'D hh:mm:ss.fraction',
'hh:mm:ss.fraction',
'hhmmss.fraction', and
hhmmss.fraction time formats, where
fraction is the fractional part in up to
microseconds (6 digits) precision. 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”.
对于以字符串形式指定的TIME值，如果字符串中包含时间部分的分隔符，则无需为小于10的小时、分钟或秒值指定两位数字。'8:3:2' 与 '08:03:02' 是相同的。For TIME values specified as
strings that include a time part delimiter, it is unnecessary
to specify two digits for hours, minutes, or seconds values
that are less than 10.
'8:3:2' is the same as
'08:03:02'.
在将 TIMESTAMP 和 DATETIME 值插入表中时，可以指定时区偏移。该偏移量附加在日期时间文字的时间部分后面，不留空格，并使用与设置time_zone 系统变量相同的格式，但有以下例外：You can specify a time zone offset when inserting
TIMESTAMP and DATETIME
values into a table. The offset is appended to the time part
of a datetime literal, with no intravening spaces, and uses
the same format used for setting the
time_zone system variable,
with the following exceptions:
对于小于 10 的小时值，需要前导零。For hour values less than 10, a leading zero is required.
值
'-00:00'被拒绝。The value'-00:00'is rejected.不能使用诸如
'EET'和'Asia/Shanghai'的时区名称；在此上下文中也不能使用'SYSTEM'。Time zone names such as'EET'and'Asia/Shanghai'cannot be used;'SYSTEM'also cannot be used in this context.
插入的值的月份部分、日期部分或两部分不能为零。无论服务器 SQL 模式设置如何，这一规则都会被强制执行。The value inserted must not have a zero for the month part, the day part, or both parts. This is enforced regardless of the server SQL mode setting.
此示例演示了如何使用不同的 time_zone 设置，将带时区偏移的日期时间值插入到 TIMESTAMP 和 DATETIME 列中，然后检索它们：This example illustrates inserting datetime values with time
zone offsets into TIMESTAMP and
DATETIME columns using different
time_zone settings, and then
retrieving them:
mysql>CREATE TABLE ts (->id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,->col TIMESTAMP NOT NULL->) AUTO_INCREMENT = 1;mysql>CREATE TABLE dt (->id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,->col DATETIME NOT NULL->) AUTO_INCREMENT = 1;mysql>SET @@time_zone = 'SYSTEM';mysql>INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'),->('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');mysql>SET @@time_zone = '+00:00';mysql>INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'),->('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');mysql>SET @@time_zone = 'SYSTEM';mysql>INSERT INTO dt (col) VALUES ('2020-01-01 10:10:10'),->('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');mysql>SET @@time_zone = '+00:00';mysql>INSERT INTO dt (col) VALUES ('2020-01-01 10:10:10'),->('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');mysql>SET @@time_zone = 'SYSTEM';mysql>SELECT @@system_time_zone;+--------------------+ | @@system_time_zone | +--------------------+ | EST | +--------------------+ mysql>SELECT col, UNIX_TIMESTAMP(col) FROM dt ORDER BY id;+---------------------+---------------------+ | col | UNIX_TIMESTAMP(col) | +---------------------+---------------------+ | 2020-01-01 10:10:10 | 1577891410 | | 2019-12-31 23:40:10 | 1577853610 | | 2020-01-01 13:10:10 | 1577902210 | | 2020-01-01 10:10:10 | 1577891410 | | 2020-01-01 04:40:10 | 1577871610 | | 2020-01-01 18:10:10 | 1577920210 | +---------------------+---------------------+ mysql>SELECT col, UNIX_TIMESTAMP(col) FROM ts ORDER BY id;+---------------------+---------------------+ | col | UNIX_TIMESTAMP(col) | +---------------------+---------------------+ | 2020-01-01 10:10:10 | 1577891410 | | 2019-12-31 23:40:10 | 1577853610 | | 2020-01-01 13:10:10 | 1577902210 | | 2020-01-01 05:10:10 | 1577873410 | | 2019-12-31 23:40:10 | 1577853610 | | 2020-01-01 13:10:10 | 1577902210 | +---------------------+---------------------+
即使在插入时使用了偏移量，选择日期时间值时也不会显示偏移量。The offset is not displayed when selecting a datetime value, even if one was used when inserting it.
支持的偏移值范围为 -13:59 到 +14:00，包括两端。The range of supported offset values is
-13:59 to +14:00,
inclusive.
包含时区偏移的日期时间字面值可以作为预处理语句的参数值。Datetime literals that include time zone offsets are accepted as parameter values by prepared statements.