MySQL 8.4 Reference Manual  /  ...  /  Date and Time Literals

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 构造来生产 DATETIMEDATETIME 值,包括如果指定了尾部小数部分。语句 TIMESTAMP 在 MySQL 中生产 DATETIME 值,因为 DATETIME 的范围更接近于标准 SQL TIMESTAMP 类型,该类型的年份范围从 00019999。 (MySQL TIMESTAMP 年份范围是 19702038。)

日期和数字字面量在日期和时间上下文中

MySQL 认识 DATE 值在这些格式中:

  • 以字符串形式,格式为 'YYYY-MM-DD''YY-MM-DD'。允许“relaxed”语法,但已弃用:任何标点符号都可以用作日期部分的分隔符。例如,'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'

  • 以数字形式,在 YYYYMMDDYYMMDD 格式下,提供该数字可以解释为日期。例如,19830905830905 解释为 '1983-09-05'

MySQL 支持DATETIMETIMESTAMP 值在这些格式下:

  • 以字符串形式在 'YYYY-MM-DD hh:mm:ss''YY-MM-DD hh:mm:ss' 格式出现。MySQL 也允许在这里使用“relaxed”语法,尽管这已经弃用:日期部分或时间部分之间可以使用任何标点符号作为分隔符。例如,'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 支持日期和时间值的任意前导或后缀空白字符,也支持 DATETIMETIMESTAMP 值之间的空白字符。在 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'

  • 以数字形式,格式为 YYYYMMDDhhmmssYYMMDDhhmmss,只要数字可以解释为日期。例如,19830905132800830905132800 解释为 '1983-09-05 13:28:00'

DATETIMETIMESTAMP 值可以包括微秒(6位)的小数部分。小数部分总是以点分隔与时间的其它部分;不承认其他小数秒 delimiter。关于 MySQL 中小数秒支持的信息,请参见第13.2.6节,“时间值中的小数秒”

包含两位年份值的日期是模糊的,因为世纪未知。 MySQL 使用以下规则解释两位年份值:

  • 年值在范围 70-99 中变为 1970-1999

  • 年值在范围 00-69 中变为 2000-2069

请参见第13.2.9节,“日期中的2位年份”

如果指定的字符串包括日期部分分隔符,那么不需要指定小于 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位长,假设它是以 YYYYMMDDYYYYMMDDhhmmss 格式,并且年份由前4位数字给出。如果数字是6或12位长,假设它是以 YYMMDDYYMMDDhhmmss 格式,并且年份由前2位数字给出。长度不在这些范围内的数字将被假设为以leading zeros填充到最接近的长度。

指定的非限定字符串根据其长度进行解释。对于长8或14个字符的字符串,首4个字符被假设为年份;否则,首2个字符被假设为年份。从左到右读取字符串,以找到年、月、日、时、分、秒值,直到字符串中出现的部分数目为止。这意味着您不应该使用少于6个字符的字符串。例如,如果指定'9903',认为表示1999年3月,MySQL将其转换为日期值。这是因为年份和月份值是9903,但日部分完全缺失。然而,您可以明确指定零值来表示缺少的月份或日部分。例如,要插入值'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'。以下格式也被理解:ssmmsshhmmss

尾部小数秒部分在 '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值到TIMESTAMPDATETIME列,然后检索它们:

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,包括两者。

包含时区偏移量的日期时间字面量被预先语句接受为参数值。