11.1.3 日期和时间字面量
日期和时间值可以以多种格式表示,例如,取决于值的确切类型和其他因素。在MySQL期望日期时,它将解释任何'2015-07-21'
、'20150721'
和 20150721
为日期。
本节描述了日期和时间字面量的可接受格式。关于临时数据类型的更多信息,例如允许值的范围,请参见第13.2节,“日期和时间数据类型”。
标准SQL和ODBC 日期和时间字面量
标准SQL要求使用类型关键字和字符串指定日期和时间字面量。关键字与字符串之间的空格是可选的。
DATE 'str'
TIME 'str'
TIMESTAMP 'str'
MySQL识别,但不像标准SQL一样,不需要类型关键字。要想实现标准兼容性,应用程序应该包含类型关键字。
MySQL也识别标准SQL语法对应的ODBC语法:
{ d 'str' }
{ t 'str' }
{ ts 'str' }
MySQL 使用 type 关键字和 ODBC 构造来生产 DATE
,TIME
和 DATETIME
值,包括如果指定了尾部小数部分。语句 TIMESTAMP
在 MySQL 中生产 DATETIME
值,因为 DATETIME
的范围更接近于标准 SQL TIMESTAMP
类型,该类型的年份范围从 0001
到 9999
。 (MySQL TIMESTAMP
年份范围是 1970
到 2038
。)
日期和数字字面量在日期和时间上下文中
MySQL 认识 DATE
值在这些格式中:
-
以字符串形式,格式为
'
或YYYY-MM-DD
''
。允许“relaxed”语法,但已弃用:任何标点符号都可以用作日期部分的分隔符。例如,YY-MM-DD
''2012-12-31'
、'2012/12/31'
、'2012^12^31'
和'2012@12@31'
等价。使用除减号 (-
) 之外的分隔符将引发警告,例如: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'
。 -
以数字形式,在
YYYYMMDD
或YYMMDD
格式下,提供该数字可以解释为日期。例如,19830905
和830905
解释为'1983-09-05'
。
MySQL 支持DATETIME
和 TIMESTAMP
值在这些格式下:
-
以字符串形式在
'
或YYYY-MM-DD hh:mm:ss
''
格式出现。MySQL 也允许在这里使用“relaxed”语法,尽管这已经弃用:日期部分或时间部分之间可以使用任何标点符号作为分隔符。例如,YY-MM-DD hh:mm:ss
''2012-12-31 11:30:45'
、'2012^12^31 11+30+45'
、'2012/12/31 11*30*45'
和'2012@12@31 11^30^45'
等价。使用其他字符作为分隔符,例如(-)日期部分和(:)时间部分,会显示警告,如下所示: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)
日期部分和时间部分之间唯一被识别的分隔符是小数点。
日期部分和时间部分可以用
T
分隔,而不是空格。例如,'2012-12-31 11:30:45'
和'2012-12-31T11:30:45'
等价。之前,MySQL 支持日期和时间值的任意前导或后缀空白字符,也支持
DATETIME
和TIMESTAMP
值之间的空白字符。在 MySQL 8.4 中,这种行为已经弃用,出现多余空白字符会显示警告,如下所示: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'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)
即使存在多个问题,也只会显示一个警告,例如以下语句序列:
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)
-
以无 delimiter 的字符串形式,格式为
'
或YYYYMMDDhhmmss
''
,只要字符串可以解释为日期。例如,YYMMDDhhmmss
''20070523091528'
和'070523091528'
解释为'2007-05-23 09:15:28'
,但'071122129015'
是非法的(分钟部分不合理),变成'0000-00-00 00:00:00'
。 -
以数字形式,格式为
YYYYMMDDhhmmss
或YYMMDDhhmmss
,只要数字可以解释为日期。例如,19830905132800
和830905132800
解释为'1983-09-05 13:28:00'
。
DATETIME
或 TIMESTAMP
值可以包括微秒(6位)的小数部分。小数部分总是以点分隔与时间的其它部分;不承认其他小数秒 delimiter。关于 MySQL 中小数秒支持的信息,请参见第13.2.6节,“时间值中的小数秒”。
包含两位年份值的日期是模糊的,因为世纪未知。 MySQL 使用以下规则解释两位年份值:
-
年值在范围
70-99
中变为1970-1999
。 -
年值在范围
00-69
中变为2000-2069
。
如果指定的字符串包括日期部分分隔符,那么不需要指定小于 10
的月或日值为两位数。例如,'2015-6-9'
等同于 '2015-06-09'
。类似地,对于指定的字符串包括时间部分分隔符,那么不需要指定小于 10
的小时、分钟或秒值为两位数。例如,'2015-10-30 1:2:3'
等同于 '2015-10-30 01:02:03'
。
指定的数字应该是6、8、12或14位长。如果数字是8或14位长,假设它是以 YYYYMMDD
或 YYYYMMDDhhmmss
格式,并且年份由前4位数字给出。如果数字是6或12位长,假设它是以 YYMMDD
或 YYMMDDhhmmss
格式,并且年份由前2位数字给出。长度不在这些范围内的数字将被假设为以leading zeros填充到最接近的长度。
指定的非限定字符串根据其长度进行解释。对于长8或14个字符的字符串,首4个字符被假设为年份;否则,首2个字符被假设为年份。从左到右读取字符串,以找到年、月、日、时、分、秒值,直到字符串中出现的部分数目为止。这意味着您不应该使用少于6个字符的字符串。例如,如果指定'9903'
,认为表示1999年3月,MySQL将其转换为“零”日期值。这是因为年份和月份值是99
和03
,但日部分完全缺失。然而,您可以明确指定零值来表示缺少的月份或日部分。例如,要插入值'1999-03-00'
,使用'990300'
。
MySQL 支持以下TIME
值格式:
-
以“非限定”语法形式的字符串。您也可以使用以下“relaxed”语法之一:
'hh:mm:ss'
,'hh:mm'
,'D hh:mm'
,'D hh'
或'ss'
。这里D
表示天数,可以有0到34的值。 -
以无分隔符的
'hhmmss'
格式提供,假设它是合法的时间。例如,'101112'
被解释为'10:11:12'
,而'109712'
是非法的(分钟部分不合法),被转换为'00:00:00'
。 -
以数字的
hhmmss
格式提供,假设它是合法的时间。例如,101112
被解释为'10:11:12'
。以下格式也被理解:ss
、mmss
或hhmmss
。
尾部小数秒部分在 'D hh:mm:ss.fraction'
、'hh:mm:ss.fraction'
、'hhmmss.fraction'
和 hhmmss.fraction
时间格式中被识别,where fraction
是小数部分,精度最多为微秒(6位)。小数部分总是与时间的其它部分用点分隔;不认可其他小数秒分隔符。关于 MySQL 中小数秒支持的信息,请参见第13.2.6节,“时间值中的小数秒”。
对于TIME
值指定为字符串,包括时间部分分隔符时,不需要指定小时、分钟或秒值小于10
的两位数字。'8:3:2'
等同于 '08:03:02'
。
您可以在插入time_zone
系统变量中指定时区偏移量,偏移量附加到datetime字面量的时间部分,使用同设置time_zone
系统变量相同的格式,但以下例外:
-
小时值小于10时,需要前导零。
-
值
'-00:00'
将被拒绝。 -
不能使用时间区名,如
time_zone
也不能在这个上下文中使用。
插入的值不能有月份部分、日部分或两部分为零,这是无论服务器SQL模式设置如何都强制执行。
以下示例展示了使用不同time_zone
设置插入datetime值到TIMESTAMP
和DATETIME
列,然后检索它们:
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 |
+---------------------+---------------------+
即使在插入时使用了偏移量,选择datetime值时也不会显示偏移量。
支持的偏移量范围是-13:59
到+14:00
,包括两者。
包含时区偏移量的日期时间字面量被预先语句接受为参数值。