查看“Mysql 日期和时间字面量”的源代码


日期和时间值可以用几种格式表示，例如带引号的字符串或数字，具体取决于值的确切类型和其他因素。例如，在MySQL期望日期的情况下，它将’2015-07-21’、’20150721’和20150721中的任何一个解释为日期。

<br>

<span id="标准sql和odbc日期和时间字面量"></span>
== 标准SQL和ODBC日期和时间字面量 ==

标准SQL要求使用类型关键字和字符串指定临时字面量。关键字和字符串之间的空格是可选的。

<pre>DATE 'str'
TIME 'str'
TIMESTAMP 'str'</pre>
MySQL与标准SQL不同，不要求类型关键字。要符合标准的应用应包括临时字面量的类型关键字。

<br>

MySQL还识别与标准SQL语法相对应的ODBC语法：

<pre>{ d 'str' }
{ t 'str' }
{ ts 'str' }</pre>
MySQL使用类型关键字和ODBC构造分别生成DATE、TIME和DATETIME值，包括指定的小数部分秒。由于DATETIME的范围更接近于标准SQL的TIMESTAMP类型（年份范围从0001到9999），因此TIMESTAMP语法在MySQL中生成DATETIME值。（MySQL的TIMESTAMP年份范围是1970年到2038年。）

<br>

== 在日期和时间上下文中的字符串和数字字面量 ==

MySQL识别以下格式的DATE值：

<ul>
<li><p>作为字符串，采用’YYYY-MM-DD’或’YY-MM-DD’格式。允许使用“宽松”的语法，但已弃用：任何标点字符都可以用作日期部分之间的分隔符。例如，‘2012-12-31’、‘2012/12/31’、’2012<sup>12</sup>31’和’2012@12@31’是等效的。从MySQL 8.0.29开始，使用除短横线（-）之外的任何字符作为分隔符都会引发警告，如下所示：</p>
<pre>  mysql&gt; SELECT DATE'2012@12@31';
  +------------------+
  | DATE'2012@12@31' |
  +------------------+
  | 2012-12-31       |
  +------------------+
  1 row in set, 1 warning (0.00 sec)

  mysql&gt; 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)</pre></li></ul>

<br>

MySQL支持以下日期和时间字面量的格式：

# 作为字符串，采用’YYYY-MM-DD’或’YY-MM-DD’格式，前提是该字符串可以作为日期解释。例如，‘20070523’和’070523’被解释为’2007-05-23’，但’071332’是非法的（因为它的月份和日期部分没有意义），它变成了’0000-00-00’。
# 作为数字，采用YYYYMMDD或YYMMDD格式，前提是该数字可以作为日期解释。例如，19830905和830905被解释为’1983-09-05’。

<br>

MySQL还识别以下格式的DATETIME和TIMESTAMP值：

# 作为字符串，采用’YYYY-MM-DD hh:mm:ss’或’YY-MM-DD hh:mm:ss’格式。MySQL也允许在这里使用一种“宽松”的语法，尽管这已经被弃用：任何标点字符都可以用作日期部分或时间部分之间的分隔符。例如，‘2012-12-31 11:30:45’、‘2012<sup>12</sup>31 11+30+45’、’2012/12/31 11''30''45’和’2012@12@31 11<sup>30</sup>45’是等效的。从MySQL 8.0.29开始，在这种值中使用除短横线（-）之外的任何字符作为分隔符都会引发警告，如下所示：

<syntaxhighlight lang="sql">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)</syntaxhighlight>
<br>

唯一被识别为日期和时间部分与小数秒部分之间的分隔符的是小数点。

日期和时间部分可以用T而不是空格分隔。例如，’2012-12-31 11:30:45’和’2012-12-31T11:30:45’是等效的。

<pre>mysql&gt; 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&gt; 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&gt; 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&gt; 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&gt; 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)</pre>
<br>

<ol start="2" style="list-style-type: decimal;">
<li><p>作为一个没有分隔符的字符串，格式为’YYYYMMDDhhmmss’或’YYMMDDhhmmss’，只要该字符串可以作为日期理解。例如，‘20070523091528’和’070523091528’被解释为’2007-05-23 09:15:28’，但’071122129015’是非法的（它的分钟部分没有意义），并变为’0000-00-00 00:00:00’。</p></li>
<li><p>作为一个数字，格式为YYYYMMDDhhmmss或YYMMDDhhmmss，只要该数字可以作为日期理解。例如，19830905132800和830905132800被解释为’1983-09-05 13:28:00’。</p></li></ol>

<br>

DATETIME或TIMESTAMP值可以包含最多到微秒（6位数字）精度的尾部小数秒部分。小数部分应始终用小数点与时间的其余部分分开；不接受其他小数秒分隔符。

<br>

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

* 70-99范围内的年份值变为1970-1999。
* 00-69范围内的年份值变为2000-2069。

<br>

对于包含日期部分分隔符的字符串值，不需要为小于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位给出。长度不是这些之一的数字将被解释为用零填充到最接近的长度。

非分隔字符串指定的值根据其长度进行解释。对于8或14个字符长的字符串，年份假定由前4个字符给出。否则，年份假定由前2个字符给出。字符串从左到右解释以找到年、月、日、小时、分钟和秒的值，字符串中存在的部分有多少就找多少。这意味着不应使用少于6个字符的字符串。例如，如果您指定’9903’，认为它代表1999年3月，MySQL将其转换为“零”日期值。这是因为年份和月份值为99和03，但日期部分完全缺失。但是，您可以明确指定零值来表示缺失的月份或日期部分。例如，要插入值’1999-03-00’，请使用’990300’。

<br>

MySQL识别以下格式的TIME值：

# 作为字符串，使用’D hh:mm:ss’格式。您还可以使用以下“宽松”语法之一：‘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。

<br>

在’D hh:mm:ss.fraction’、‘hh:mm:ss.fraction’、’hhmmss.fraction’和hhmmss.fraction时间格式中，可以识别尾部的小数秒部分，其中fraction是以微秒（6位数字）精度表示的小数部分。小数部分应始终用小数点与时间的其余部分分开；不接受其他小数秒分隔符。

对于作为字符串指定的TIME值，如果包含时间部分分隔符，则无需为小于10的小时、分钟或秒值指定两位数。’8:3:2’与’08:03:02’相同。

<br>

从MySQL 8.0.19开始，当将TIMESTAMP和DATETIME值插入表中时，可以指定时区偏移量。偏移量附加到datetime字面量的时间部分，中间没有空格，并使用与设置time_zone系统变量相同的格式，但有以下例外：

* 对于小于10的小时值，需要前导零。
* 值’-00:00’被拒绝。
* 时区名称如’EET’和’Asia/Shanghai’不能使用；在此上下文中也不能使用’SYSTEM’。
* 插入的值的月份部分、日期部分或两者都不能为零。从MySQL 8.0.22开始强制执行此规则，无论服务器SQL模式设置如何。

<br>

以下示例说明了如何使用不同的time_zone设置将带有时区偏移量的datetime值插入TIMESTAMP和DATETIME列，然后检索它们：

<pre>mysql&gt; CREATE TABLE ts (
    -&gt;     id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -&gt;     col TIMESTAMP NOT NULL
    -&gt; ) AUTO_INCREMENT = 1;

mysql&gt; CREATE TABLE dt (
    -&gt;     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -&gt;     col DATETIME NOT NULL
    -&gt; ) AUTO_INCREMENT = 1;

mysql&gt; SET @@time_zone = 'SYSTEM';

mysql&gt; INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'),
    -&gt;     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');

mysql&gt; SET @@time_zone = '+00:00';

mysql&gt; INSERT INTO ts (col) VALUES ('2020-01-01 10:10:10'),
    -&gt;     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');

mysql&gt; SET @@time_zone = 'SYSTEM';

mysql&gt; INSERT INTO dt (col) VALUES ('2020-01-01 10:10:10'),
    -&gt;     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');

mysql&gt; SET @@time_zone = '+00:00';

mysql&gt; INSERT INTO dt (col) VALUES ('2020-01-01 10:10:10'),
    -&gt;     ('2020-01-01 10:10:10+05:30'), ('2020-01-01 10:10:10-08:00');

mysql&gt; SET @@time_zone = 'SYSTEM';

mysql&gt; SELECT @@system_time_zone;
+--------------------+
| @@system_time_zone |
+--------------------+
| EST                |
+--------------------+

mysql&gt; 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&gt; 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 |
+---------------------+---------------------+</pre>
<br>

选择datetime值时，即使插入时使用了偏移量，也不会显示偏移量。

支持的偏移量范围是-13:59到+14:00，包括这两个值。

包含时区偏移量的datetime字面量可以作为预处理语句的参数值接受。

<br>