1 概述

MySQL 用 datetime, timestamp 数据类型（为了简便，本文用 datetime2 表示 datetime 和 timestamp）存储时刻。从 MySQL 应用的角度看，使用 datetime2 数据类型涉及 2 个核心问题：

• 应用如何生成 SQL 语句中的 datetime2 值？比如，若要把 ‘2022-07-18 09:36:25+08:00’ 存入 datetime 列，则 SQL 语句中的插入值应该是 ‘2022-07-18 09:36:25’，’2022-07-18 01:36:25’，还是其他值？
• 应用如何解释从 MySQL 取出的 datetime2 值？比如，若应用从 MySQL 取出的 datetime 列值为 ‘2022-07-18 09:36:25’，则应用应该认为该值表示 ‘2022-07-18 09:36:25+08:00’，’2022-07-18 09:36:25+00:00’，还是其他值？

要搞清以上问题，就要理解 datetime2 的原理，而 MySQL 手册并未完整、清晰地表达该原理。本文通过参考 MySQL 手册和实验验证，研究 datetime2 的原理，以便正确使用 datetime2 。

2 环境

操作系统: Windows 10 教育版，版本号 21H1，内部版本号 19043.1165

MySQL: 8.0.29 MySQL Community Server - GPL

Java: openjdk 11 2018-09-25

mysql-connector-java: 8.0.29

3 原理

3.1 概念

图3.1-1 应用与 MySQL 数据交互流程

• 应用系统（简称应用）： 蕴含业务逻辑的应用软件或人。
• 业务值： 具有业务含义的值。在应用与 MySQL 的数据交互中，应用负责将业务值转换成 MySQL 输入值，将 MySQL 输出值转换成业务值。MySQL 对业务值没有感知。
• MySQL 输入值（简称输入值）： MySQL 服务端接收到的 SQL 语句中的值。
• MySQL 输出值（简称输出值）： MySQL 服务端返回的 SQL 语句执行结果中的值。
• MySQL 存储值（简称存储值）： MySQL 存储引擎中存储的值。
• MySQL 解释值（简称解释值）： MySQL 在计算过程中将一个可能含义不明的值解释成的含义明确的值。
• MySQL 会话时区（简称会话时区）： MySQL 会话的 time_zone 系统变量代表的时区。

3.2 MySQL 怎么表示 datetime2 输入值，输出值

datetime2 输入值基本用 ‘YYYY-MM-DD hh:mm:ss’ 格式表示，但是存在各种变体（比如在 SQL 语法层面，可以用字符串和数字表示，若用字符串表示，则分隔符 ‘-‘, ‘ ‘, ‘:’ 可省略或替换成其他字符），详情参见 MySQL 参考手册 9.1.3 Date and Time Literals。自 MySQL 8.0.19 起，可以在 datetime2 输入值中指定时区，如 ‘2022-07-18 09:36:25+08:00’。

datetime2 输出值用 ‘YYYY-MM-DD hh:mm:ss’ 格式表示，如 ‘2022-07-18 09:36:25’。

3.3 MySQL 怎么解释 datetime2 输入值

若 datetime2 输入值中不含时区，则 MySQL 将其解释为会话时区的时刻。例如，输入值为 ‘2022-07-18 09:36:25’，若会话时区为 ‘+00:00’，解释值为 ‘2022-07-18 09:36:25+00:00’，若会话时区’+08:00’，则解释值为 ‘2022-07-18 09:36:25+08:00’。

若 datetime2 输入值中包含时区，则 MySQL 将其解释成其自身表示的时刻，而忽略会话时区。例如，输入值为 ‘2022-07-18 01:36:25+00:00’，则无论会话时区是什么，解释值都是 ‘2022-07-18 01:36:25+00:00’，。

3.4 MySQL 怎么生成 datetime2 存储值

MySQL 存入 datetime2 值的过程中，遵循原则：存储值的解释值等于输入值的解释值。MySQL 生成存储值的步骤如下：

1. 先将 datetime2 输入值转换成解释值。假设解释值为 ‘2022-07-18 01:36:25+00:00’
2. 然后将解释值转换成存储值
   1. 对于 datetime 类型，将解释值转换成会话时区的时刻，即为存储值。例如，若会话时区为 ‘+00:00’，则存储值为 ‘2022-07-18 01:36:25’，若会话时区为 ‘+08:00’，则存储值为 ‘2022-07-18 09:36:25’。注意，上例中的存储值表示法只是与实际存储值在表示时刻方面等效，并不代表实际存储值的数据结构，下面的 timestamp 存储值同理。
   2. 对于 timestamp 类型，忽略会话时区，将解释值转换成 UTC 时刻，即为存储值。所以无论会话时区是什么，存储值都是 ‘2022-07-18 01:36:25’。

3.5 MySQL 怎么解释 datetime2 存储值

MySQL 将 datetime 存储值解释成会话时区的时刻，如果会话时区变化，则解释值也会变化。例如， 存储值为 ‘2022-07-18 09:36:25’，若会话时区为 ‘+00:00’ ，则解释值为 ‘2022-07-18 09:36:25+00:00’，若会话时区为 ‘+08:00’ ，则解释值为 ‘2022-07-18 09:36:25+08:00’。

MySQL 将 timestamp 存储值解释成 UTC 时刻。例如，存储值为 ‘2022-07-18 01:36:25’，无论会话时区是什么，解释值都是 ‘2022-07-18 01:36:25+00:00’。

3.6 MySQL 怎么生成 datetime2 输出值

MySQL 取出 datetime2 值的过程中，遵循原则：输出值的解释值等于存储值的解释值。MySQL 将 datetime2 存储值转换成解释值，再将解释值转换成会话时区的时刻，即为 datetime2 输出值（表述1）。

对于 datetime，因为存储值转换成解释值，和解释值转换成会话时区的时刻 2 个过程中，会话时区是一样的，所以实际上输出值永远等于存储值。所以 MySQL 生成 datetime 输出值的规则也可表述为：datetime 存储值即为输出值（表述 2）。

对于 datetime，这 2 种表述是等价的，表述1的好处是其与 MySQL 生成 timestamp 输出值的规则的表述一致（有点多态的感觉），表述2的好处是简洁直观。

3.7 MySQL 怎么计算 datetime2 列的比较条件值

对于 SQL 语句中 datetime2 列与指定 datetime2 值（称为比较值）的比较（比较运算符为 ‘=’, ‘!=’, ‘<’, ‘>’, ‘in’, ‘between’ 等）条件（如 where a = ‘2022-07-18 01:36:25’，其中 a 列为 datetime2 类型），MySQL 通过比较 datetime2 存储值的解释值和比较值的解释值来计算条件值（这里仅针对条件值的计算结果，而不针对其计算算法）。

4 原理演示

4.1 场景设置

演示表的结构如下，其中 datetime_demo, timestamp_demo 分别演示 datetime, timestamp 类型。

CREATE TABLE `datetime_demo` (
  `id` int NOT NULL,
  `a` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
CREATE TABLE `timestamp_demo` (
  `id` int NOT NULL,
  `a` timestamp NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

4.2 datetime 演示

4.2.1 演示方案

在 mysql 程序（https://dev.mysql.com/doc/refman/8.0/en/mysql.html）中运行下列 SQL 语句：

方案解释：

1. 2-5 步在不同会话时区下，插入 datetime 值（a 列的值）。所有记录的 a 列业务值都是 ‘2022-07-18 01:36:25+00:00’，业务值转换成输入值的规则是，确保输入值的解释值等于业务值。
2. 6-9 步在不同会话时区下，获取 2-5 步插入的 datetime 值的输出值（a 列的值）和 Unix 时间戳（unix_timestamp(a) ）。datetime 输出值演示了 3.3-3.6 节的 datetime 处理规则。Unix 时间戳更直接地演示了 datetime 存储值的解释规则。
3. 10-13 步演示在不同会话时区下，为了查询 a 列业务值等于 ‘2022-07-18 01:36:25+00:00’ 的记录，应该如何生成查询条件的 a 列比较值。这同时也演示了 3.7 节的 datetime 处理规则。表中的记录是在不同时区下插入的，因而 a 列存储值与业务值的转换规则不同，无法在全表范围查询 a 列业务值等于 ‘2022-07-18 01:36:25+00:00’ 的记录。所以，11, 12 步用 “id between 1 and 2” 查询条件将查询范围限定在插入时会话时区为 +00:00 的记录上。

4.2.2 演示结果

图4.2-1 6-9 步的结果

图4.2-2 10-13 步的结果

4.2.3 演示分析

表4.2-1 2-5 步存入过程中 a 列值的转换

• id 为 1 的记录，a 列输入值 ‘2022-07-18 01:36:25’ 不含时区，故解释值为会话时区（+00:00）的时刻，即 ‘2022-07-18 01:36:25+00:0’。id 为 3 的记录同理。（参见 3.3 MySQL 怎么解释 datetime2 输入值）
• id 为 2，4 的记录，a 列输入值 ‘2022-07-18 06:36:25+05:00’ 包含时区，所以无论会话时区是什么，解释值都是输入值自身表示的时刻，即 ‘2022-07-18 06:36:25+05:00’。（参见 3.3 MySQL 怎么解释 datetime2 输入值）
• id 为 2，4 的记录，将 a 列解释值转换成存储值时，a 列解释值相同（均为 ‘2022-07-18 06:36:25+05:00’），但会话时区不同（分别为 +00:00, +08:00），所以 a 列存储值不同（分别为 ‘2022-07-18 01:36:25’，’2022-07-18 09:36:25’）。（参见 3.4 MySQL 怎么生成 datetime2 存储值）

表4.2-2 6-9 步取出过程中 a 列值的转换

• id 为 1 的记录， a 列存储值为 ‘2022-07-18 01:36:25’，a 列解释值为会话时区的时刻，当会话时区为 +00:00 时，a 列解释值为 ‘2022-07-18 01:36:25+00:00’，当会话时区为 +08:00 时，a 列解释值为 ‘2022-07-18 01:36:25+08:00’。其他记录同理。（参见 3.5 MySQL 怎么解释 datetime2 存储值）
• a 列输出值总是等于 a 列存储值。（参见 3.6 MySQL 怎么生成 datetime2 输出值）
• a 列解释值与 unix_timestamp(a) 表示相同时刻。
• 关于 a 列业务值
  • 对于 id 为 1 的记录，应用（这里的应用是人）根据 a 列输出值 ‘2022-07-18 01:36:25’，计算出 a 列存储值 ‘2022-07-18 01:36:25’，又根据插入该记录时的会话时区 +00:00，计算出插入该记录时 a 列输入值的解释值为该时区的存储值，即 ‘2022-07-18 01:36:25+00:00’。根据演示方案解释，a 列业务值等于插入时 a 列输入值的解释值，故 a 列业务值为 ‘2022-07-18 01:36:25+00:00’。
  • 同理，对于 id 为 3 的记录，应用计算出 a 列业务值为 ‘2022-07-18 09:36:25+08:00’，即 ‘2022-07-18 01:36:25+00:00’。
  • 应用根据 datetime 输出值生成业务值，与存储(insert, update 语句)时会话时区有关，与查询时会话时区无关。

下面分析 10-13 步怎么生成查询条件的 a 列比较值。

• 在插入时会话时区为 +00:00 的情况下，a 列业务值 ‘2022-07-18 01:36:25+00:00’（下称目标业务值） 对应存储值 ‘2022-07-18 01:36:25’（下称目标存储值）。根据 3.7 节的规则，要查询 a 列业务值等于目标业务值的记录，就要让 a 列比较值的解释值等于目标存储值的解释值。
• 10-11 步，会话时区为 +00:00，目标存储值的解释值为 ‘2022-07-18 01:36:25+00:00’，比较值的解释值应该等于该值，所以比较值可以是 ‘2022-07-18 01:36:25’，’2022-07-18 06:36:25+05:00’ 等。
• 12-13 步，会话时区为 +08:00，目标存储值的解释值为 ‘2022-07-18 01:36:25+08:00’，比较值的解释值应该等于该值，所以比较值可以是 ‘2022-07-18 01:36:25’，’2022-07-17 22:36:25+05:00’ 等。

4.3 timestamp 演示

4.3.1 演示方案

在 mysql 程序中运行下列 SQL 语句：

方案解释：

1. 2-5 步在不同会话时区下，插入 timestamp 值（a 列的值）。所有记录的 a 列业务值都是 ‘2022-07-18 01:36:25+00:00’，业务值转换成输入值的规则是，确保输入值的解释值等于业务值。
2. 6-9 步在不同会话时区下，获取 2-5 步插入的 timestamp 值的输出值（a 列的值）和 Unix 时间戳（unix_timestamp(a) ）。timestamp 输出值演示了 3.3-3.6 节的 timestamp 处理规则。Unix 时间戳更直接地演示了 timestamp 存储值的解释规则。
3. 10-13 步演示在不同会话时区下，为了查询 a 列业务值等于 ‘2022-07-18 01:36:25+00:00’ 的记录，应该如何生成查询条件的 a 列比较值。这同时也演示了 3.7 节的 timestamp 处理规则。

4.3.2 演示结果

图4.3-1 6-9 步的结果

图4.3-2 10-13 步的结果

4.3.3 演示分析

表4.3-1 2-5 步存入过程中 a 列值的转换

• id 为 1 的记录，a 列输入值 ‘2022-07-18 01:36:25’ 不含时区，故解释值为会话时区（+00:00）的时刻，即 ‘2022-07-18 01:36:25+00:00’。id 为 3 的记录同理。（参见 3.3 MySQL 怎么解释 datetime2 输入值）
• id 为 2，4 的记录，a 列输入值 ‘2022-07-18 06:36:25+05:00’ 包含时区，所以无论会话时区是什么，解释值都是输入值自身表示的时刻，即 ‘2022-07-18 06:36:25+05:00’。（参见 3.3 MySQL 怎么解释 datetime2 输入值）
• 所有记录的 a 列解释值等于 ‘2022-07-18 01:36:25+00:00’，故转换成存储值（即 UTC 时刻）都是 ‘2022-07-18 01:36:25’。（参见 3.4 MySQL 怎么生成 datetime2 存储值）

表4.2-2 6-9 步取出过程中 a 列值的转换

• 所有记录的 a 列存储值都是 ‘2022-07-18 01:36:25’，而 timestamp 存储值总是解释成 UTC 时刻，所以所有记录的 a 列解释值都是 ‘2022-07-18 01:36:25+00:00’。（参见 3.5 MySQL 怎么解释 datetime2 存储值）
• a 列解释值转换成会话时区的时刻，即为输出值。所以，当会话时区为 +00:00 时，输出值为 ‘2022-07-18 01:36:25’，当会话时区为 +08:00 时，输出值为 ‘2022-07-18 09:36:25’。（参见 3.6 MySQL 怎么生成 datetime2 输出值）
• a 列解释值与 unix_timestamp(a) 表示相同时刻。
• 关于 a 列业务值
  • 当会话时区为 +00:00 时，a 列输出值 ‘2022-07-18 01:36:25’，应用计算出 a 列输出值的解释值（即会话时区的输出值） ‘2022-07-18 01:36:25+00:00’。对于 timestamp 类型，输出值的解释值总是等于存储值的解释值，并等于插入时输入值的解释值。所以插入时输入值的解释值为 ‘2022-07-18 01:36:25+00:00’。根据演示方案解释，a 列业务值等于插入时 a 列输入值的解释值，故 a 列业务值为 ‘2022-07-18 01:36:25+00:00’。
  • 同理，当会话时区为 +08:00 时，应用计算出 a 列业务值为 ‘2022-07-18 09:36:25+08:00’，即 ‘2022-07-18 01:36:25+00:00’。
  • 应用根据 timestamp 输出值生成业务值，与查询时会话时区有关，与存储(insert, update 语句)时会话时区无关。

下面分析 10-13 步怎么生成查询条件的 a 列比较值。

• a 列业务值 ‘2022-07-18 01:36:25+00:00’（下称目标业务值） 对应存储值 ‘2022-07-18 01:36:25’（下称目标存储值）。根据 3.7 节的规则，要查询 a 列业务值等于目标业务值的记录，就要让 a 列比较值的解释值等于目标存储值的解释值。
• 对于 timestamp 类型，存储值的解释值是固定的，目标存储值的解释值为 ‘2022-07-18 01:36:25+00:00’。所以，无论查询时会话时区是什么，只要确保 a 列比较值的解释值等于 ‘2022-07-18 01:36:25+00:00’ 即可。
• 10-11 步，会话时区为 +00:00，比较值可以是 ‘2022-07-18 01:36:25’, ‘2022-07-18 06:36:25+05:00’ 等。
• 12-13 步，会话时区为 +08:00，比较值可以是 ‘2022-07-18 09:36:25’, ‘2022-07-18 06:36:25+05:00’ 等。

4.4 用 JDBC API 演示

4.2-4.3 节用 mysql 程序演示，本节用基于 JDBC API 的 Java 程序演示。

Java 程序地址： https://gitee.com/zixiu1991/mysql-datetime-demo

Java 程序执行的 SQL 语句与 4.2-4.3 节相同。

值得一提的是，Java 程序获取 datetime2 输出值的方式是 java.sql.ResultSet#getString(int | String) 方法，而不是 ResultSet#getDate(int | String) 方法。这是为了获得原始的 datetime2 输出值。

演示结果与 4.2-4.3 相同。

5 实践规约

本节给出 datetime2 的实践规约，以便正确使用 datetime2。

5.1 可还原原则

使用任何数据存储系统，都应遵循可还原原则：可将数据存储系统中的存储值还原成业务值。

比如 MySQL 表中有一个 tinyint 类型的 sex 列，表示性别。应用中某些部分在存入 sex 列值时，业务值转换成存储值的规则为：男 -> 1，女 -> 0，而其他部分在存入 sex 列值时，业务值转换成存储值的规则为：男 -> 0，女 -> 1。则以上场景违背可还原原则，因为 sex 列存储值中，有些 1 代表男，有些 1 代表女，有些 0 代表男，有些 0 代表女，无法从 sex 列存储值还原出业务值。

MySQL 数值，字符串类型业务值与存储值的转换规则比较简单，一般不会在这些类型上违背可还原原则。而 datetime2 类型业务值与存储值的转换规则较为复杂，不懂其原理者，容易违背可还原原则。

5.2 实践规约

下面根据 datetime2 的原理和可还原原则，给出 datetime2 的实践规约。

1 【强制】将数据库的使用规约（不限于针对 datetime2 类型），写在数据库设计文档上。

说明：这是为了显式声明关于数据库中数据的含义和使用方式的契约，任何共用同一个数据库的应用，只要遵守契约，就能保障可还原原则。不要让契约只蕴含在应用软件源码里，或人脑里。

2 【推荐】所有 MySQL 会话使用相同且固定的时区。

3 【强制】datetime2 输入值使用不含时区格式，不使用含时区格式。下面的规约基于该规约。

说明：若 datetime2 输入值混用 2 种格式，会使 datetime2 的使用规则复杂化，所以只使用 1 种格式较好。而含时区格式输入值自 MySQL 8.0.19 起才被支持，使用不含时区格式，能适配更多 MySQL 版本。

4【强制】对于 datetime 类型，业务值转输入值规则为：输入值 = datetime_format(固定时区，业务值)。

说明：datetime_format(a，b) 表示将时刻 b 格式化成时区 a 的时刻表示，如 datetime_format(‘+00:00’, ‘2022-07-18 09:36:25+08:00’) = ‘2022-07-18 01:36:25’。 固定时区是一个常量，固定时区与存入时会话时区（指执行存入（insert/update 语句）操作时的会话时区）没有关系，二者可以不相等。该规约保障可还原原则。

反例：业务值转输入值规则：输入值 = datetime_format(变化时区，业务值)。这违背可还原原则。

5 【强制】对于 datetime 类型，输出值转业务值规则为：业务值 = datetime_parse(固定时区，输出值)，其中固定时区等于规约4 中的固定时区。该规约基于规约 4。

说明：datetime_parse(a, b) 表示将不含时区的时刻 b 解析成时区 a 的时刻，如 datetime_parse(‘+08:00’, ‘2022-07-18 09:36:25’) = ‘2022-07-18 09:36:25+08:00’。

6 【强制】对于 timestamp 类型，业务值转输入值规则为：输入值 = datetime_format(存入时会话时区，业务值)。

说明：该规约既保障可还原原则，又使得输出值转业务值规则简单。

反例：业务值转输入值规则为：输入值 = datetime_format(固定时区，业务值)，且存入时会话时区不固定。这违背可还原原则。

反例：业务值转输入值规则为：输入值 = datetime_format(固定时区，业务值)，存入时会话时区固定且不等于固定时区。这保障可还原原则，但使得输出值转业务值规则复杂。 在此场景下，输出值转业务值规则为：业务值 = datetime_format(取出时会话时区， 输出值) + (存入时会话时区 - 固定时区)，其中，取出时会话时区指执行取出（select 语句）操作时的会话时区。举例，假设固定时区为 ‘+08:00’，存入时会话时区为 ‘+00:00’，存入时业务值为 ‘2022-07-18 09:36:25+08:00’，则输入值 = datetime_format(‘+08:00’, ‘2022-07-18 09:36:25+08:00’) = ‘2022-07-18 09:36:25’ 。若取出时会话时区也是 ‘+00:00’，则输出值 = ‘2022-07-18 09:36:25’，业务值 = datetime_format(‘+00:00’, ‘2022-07-18 09:36:25’) + (‘+00:00’ - ‘+08:00’) = ‘2022-07-18 09:36:25+08:00’。从上例可见输出值转业务值规则比较复杂，特别是当人直接查询数据库时，需要人工计算才能还原出业务值，很麻烦。

7 【强制】对于 timestamp 类型，输出值转业务值规则为： 业务值 = datetime_parse(取出时会话时区，输出值)。该规约基于规约 6。

8 【强制】应用软件必须确保业务值转输入值，输出值转业务值按上述相关规约执行。

说明：应用软件执行业务值转输入值，输出值转业务值操作时，可能引用库（如 Java 中的 java.text.SimpleDateFormat 类和 MyBatis）的时间格式化/解析功能。而时间格式化/解析功能的效果与执行该功能时采用的时区有关系，应用必须确保时间格式化/解析功能的效果符合上述相关规约。

反例：在某 Java 应用中，用 java.util.Date 对象表示业务值，用 java.text.SimpleDateFormat 类的 format, parse 方法执行业务值转输入值，输出值转业务值操作。而执行 SimpleDateFormat 类 的 format, parse 方法时使用的是默认时区：操作系统时区。对于 datetime 类型，当该应用运行在 +00:00 时区的操作系统上时，输入值 = datetime_format(+00:00, 业务值)，当该应用运行在 +08:00 时区的操作系统上时，输入值 = datetime_format(+08:00, 业务值)，这违背可还原原则（参见规约 4 的反例）。