11.2.2 DATE，DATETIME，和 TIMESTAMP 类型

官方文档地址：11.2.2 The DATE, DATETIME, and TIMESTAMP Types

---

DATE、DATETIME 和 TIMESTAMP 类型是相关的。本节介绍它们的特点，它们有何相似之处，又有何不同。MySQL 可以识别几种格式的 DATE，DATETIME 和 TIMESTAMP 值，参见 9.1.3 日期和时间字面量。对于 DATE 和 DATETIME 范围的描述，supported意味着虽然早期的值可以工作，但不能保证。

DATE 类型用于有日期部分但没有时间部分的值。MySQL 以'YYYY-MM-DD'格式检索并显示 DATE 值。支持的范围是'1000-01-01'到'9999-12-31'。

DATETIME 类型用于同时包含日期和时间部分的值。MySQL 以'YYYY-MM-DD hh:mm:ss'格式显示 DATETIME 值，支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。

TIMESTAMP 数据类型用于同时包含日期和时间部分的值。TIMESTAMP 的范围为 UTC '1970-01-01 00:00:01'到 UTC '2038-01-19 03:14:07'。

DATETIME 或 TIMESTAMP 值可以包含末尾的小数秒部分，精度最高可达微秒（6位）。特别是，插入到 DATETIME 或 TIMESTAMP 列中的值的任何小数部分都将被存储，而不是丢弃。如果包含了小数部分，这些值的格式为'YYYY-MM-DD hh:mm:ss[.fraction]'，DATETIME 的范围是'1000-01-01 00:00:00.000000'到'9999-12-31 23:59:59.999999'，TIMESTAMP 的范围是'1970-01-01 00:00:01.000000'到'2038-01-19 03:14:07.999999'。小数秒部分总是与其余部分用小数点隔开；不识别其他小数秒分隔符。关于 MySQL 对小数秒的支持，参见 11.2.6 时间值的小数秒。

TIMESTAMP 和 DATETIME 数据类型提供了自动初始化和更新到当前日期和时间的功能。参见 11.2.5 TIMESTAMP 和 DATETIME 的自动初始化和更新。

MySQL 将 TIMESTAMP 值从当前时区转换为 UTC 进行存储，并从 UTC 返回到当前时区进行检索。（其他类型，如 DATETIME 不会出现这种情况。）默认情况下，每个连接的当前时区是服务器时间。可以在每个连接的基础上设置时区。只要时区设置保持不变，就会返回和所存储值相同的值。如果存储一个 TIMESTAMP 值，然后更改时区并检索该值，则检索到的值与存储的值不同。出现这种情况是因为没有在两个方向上使用相同的时区进行转换。系统变量time_zone的值可以作为当前时区。参见 5.1.15 MySQL 服务器时区支持。

从 MySQL 8.0.19 开始，当向表中插入 TIMESTAMP 和 DATETIME 值时，可以指定时区偏移量。偏移量追加到日期和时间文本的时间部分，不包含空格，并使用与设置系统变量time_zone相同的格式，除以下例外情况外：

• 如果小时值小于 10，则需要一个前导零。
• 值'-00:00'是被拒绝的。
• 不能使用'EET'和'Asia/Shanghai'等时区名称；'SYSTEM'也不能在这个上下文中使用。

插入的值的月、日或这两个部分不能为零。从 MySQL 8.0.22 开始，无论服务器的 SQL 模式如何设置，这都是强制的。

这个例子演示了使用不同的time_zone设置将带有时区偏移量的日期时间值插入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 |
+---------------------+---------------------+

当选择一个日期时间值时，偏移量不会显示，即使在插入时使用了偏移量。

支持的偏移量取值范围为-14:00 ~ +14:00。

预处理语句接受包含时区偏移量的日期时间文本作为参数值。

无效的 DATE、DATETIME 或TIMESTAMP 值将被转换为适当类型的“零”值（'0000-00-00'或者'0000-00-00 00:00:00'），如果 SQL 模式允许这种转换。精确的行为取决于启用哪一种严格的SQL模式和NO_ZERO_DATE SQL 模式；参见 5.1.11 服务器 SQL 模式。

在 MySQL 8.0.22 及以后版本中，你可以使用CAST()和AT TIME ZERO操作符将 TIMESTAMP 值转换为UTC DATETIME 值，如下所示：

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 |
+---------------------+---------------------+

有关语法和其他示例的完整信息，参见CAST()函数的描述。

注意 MySQL 中日期值解释的某些特性：

• MySQL 允许以字符串形式指定值的“放松”格式，其中任何标点字符都可以用作日期部分或时间部分的分隔符。在某些情况下，这种语法可能具有欺骗性。例如，像'10:11:12'这样的值可能看起来像一个时间值，因为:，但如果在日期上下文中使用，则会被解释为日期值'2010-11-12'。值'10:45:15'被转换为'0000-00-00'，因为'45'不是有效的月份。
  
  日期和时间部分和小数部分之间唯一可识别的分隔符是小数点。
• 服务器要求月和日的值是有效的，而不仅仅是分别在1到12和1到31的范围内。禁用严格模式后，无效日期（如'2004-04-31'）会被转换为'0000-00-00'，并生成一个警告。启用严格模式时，无效日期会生成错误。要允许这样的日期，启用ALLOW_INVALID_DATES，参见5.1.11 服务器 SQL 模式。
• MySQL 不接受在日或月列中包含0的 TIMESTAMP 值，也不接受不是有效日期的值。唯一的例外是特殊的“零”值'0000-00-00 00:00:00'，如果 SQL 模式允许这个值。精确的行为取决于启用哪一个，严格 SQL 模式和NO_ZERO_DATE SQL 模式；参见 5.1.11 服务器 SQL 模式。
• 包含两位数年份值的日期是不明确的，因为世纪是未知的。MySQL 使用以下规则来解释2位数字的年份值，参见 11.2.8 两位数字的年份：
  • 年值在00-69的范围将被解释为2000-2069。
  • 年值在70-99的范围将被解释为1970-1999。