关于mysql的时区（上）：关于mysql的datetime和timestamp类型是否能根据时区不同自动转换值

关于mysql的时区

背景

由于mysql5.7用的人还是比较多，本文还是基于mysql 5.7（不过跟8.x版本应该是差不多的）

本问研究mysql的

• 常用的两个日期类型和时区的关系

  • datetime
  • timestamp

  在阿里的Java开发手册中，推荐的是用datetime，没说明原因，估计是忌惮timestamp的最大值只能到2038年。

• 顺便回顾了怎么设置mysql的时区、CST的bug

一、mysql 的 日期类型和时区的关系

The session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the session time zone to UTC for storage, and from UTC to the session time zone for retrieval.

The session time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values. If you want locale-specific arithmetic for DATE, TIME, or DATETIME values, convert them to UTC, perform the arithmetic, and then convert back.

我们常用的关于日期/时间的类型有

• datetime（也包括date、time）
• timestamp

1、总结

1）timestamp

按官方的说法，timestamp存储的是UTC时间戳，展示的时候会将时间戳转为当前时区进行展示。

例如有个字段是在东八区的时候插入的，东八区时候select出来是 2021-04-04 21:51:43，如果数据库的时区变成东九区，比如设置会话时区为东九区set time_zone='+9:00'，则select查出的值是2021-04-04 22:51:43。

要不是因为timestamp的范围过小，最大值只能到2038年，我觉得timestamp是最好的最方便的类型

总结：反正只要知道timestamp存的是时间戳就可以了。

2）datetime、date、time

这些类型的字段，官方也说明白了，就是不会帮你转换。说白了就跟写死的字符串一样，存进去是什么样的值，不管你的时区调成什么样都不会再改变了。

总结：这些类型存的是死值，跟死的字符串一样。

3）now()、curtime()、curdate()

这些函数的值，是会随着时区的不同而获取到不同的值的。

2、动手实验

用命令行（或者Navicat也行）连上mysql，我的数据库的时区如下

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +08:00 |
+------------------+--------+
2 rows in set (0.00 sec)

创建库、选中库、创建表

mysql> create database t_test_tz;
Query OK, 1 row affected (0.00 sec)

mysql> use t_test_tz
Database changed
mysql> create table t_test(id int primary key auto_increment,datetime datetime,timestamp timestamp,str varchar(255));
Query OK, 0 rows affected (0.01 sec)

mysql> desc t_test;
+-----------+--------------+------+-----+-------------------+-----------------------------+
| Field     | Type         | Null | Key | Default           | Extra                       |
+-----------+--------------+------+-----+-------------------+-----------------------------+
| id        | int(11)      | NO   | PRI | NULL              | auto_increment              |
| datetime  | datetime     | YES  |     | NULL              |                             |
| timestamp | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| str       | varchar(255) | YES  |     | NULL              |                             |
+-----------+--------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.01 sec)

mysql> 

往库里加数据

mysql> insert into t_test(datetime,timestamp,str) values(now(),now(),'2021-04-05 17:25:42 东八区，字符串类型，方便切换时区时比较该值和另外两个字段');                      
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_test;
+----+---------------------+---------------------+----------------------------------------------------------------------------------------------------------+
| id | datetime            | timestamp           | str                                                                                                      |
+----+---------------------+---------------------+----------------------------------------------------------------------------------------------------------+
|  1 | 2021-04-05 17:25:42 | 2021-04-05 17:25:42 | 2021-04-05 17:25:42 东八区，字符串类型，方便切换时区时比较该值和另外两个字段                             |
+----+---------------------+---------------------+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

说明：str这个字段仅仅是用来记录，因为是字符串，这样无论怎么切换时区，这个字段的值都不会变化，方便后续调整数据库时区的时候和datetime和timestamp字段进行比较。

设置会话的时区为东九区，可以看到会话的时区已经变成东九区（全局时区依然是东八区，这个没有关系）

mysql> select @@GLOBAL.time_zone,@@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| +08:00             | +08:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone='+9:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@GLOBAL.time_zone,@@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| +08:00             | +09:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql>

再次查看数据

mysql> select * from t_test;
+----+---------------------+---------------------+----------------------------------------------------------------------------------------------------------+
| id | datetime            | timestamp           | str                                                                                                      |
+----+---------------------+---------------------+----------------------------------------------------------------------------------------------------------+
|  1 | 2021-04-05 17:25:42 | 2021-04-05 18:25:42 | 2021-04-05 17:25:42 东八区，字符串类型，方便切换时区时比较该值和另外两个字段                             |
+----+---------------------+---------------------+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 

对比str，我们插入时的时间是东八区17点，但是看到timestamp的值已经变成了18点，快了一个小时，而datetime的值没有变化。

这就证明了官网说的，datetime存的是死值，不会随着时区的切换而变化，而timestamp存的是UTC时间戳，当时区调整后，也会输出跟时区相符合的字符串值。

而对于now() 等等函数，在东八区和东九区，输出的时区是不一样的，下面这几行命令的执行绝对不超过1小时，而看得到 select now() ` 的值相差1小时，这就是说now()这个函数也是会根据时区输出跟时区相匹配的字符串

mysql> select @@GLOBAL.time_zone,@@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| +08:00             | +08:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-04-05 17:33:23 |
+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone='+9:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@GLOBAL.time_zone,@@SESSION.time_zone;
+--------------------+---------------------+
| @@GLOBAL.time_zone | @@SESSION.time_zone |
+--------------------+---------------------+
| +08:00             | +09:00              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-04-05 18:33:43 |
+---------------------+
1 row in set (0.00 sec)

mysql> 

上述的查询结果是通过命令行输出的结果，如果通过Java程序输出，就更加复杂了，比如通过springboot+mybatis查询上述 t_test 表，查出的Date字段的值会做什么样的转换，这个有点复杂，下次有空再研究。

初步来说有这么几个

• 数据库可以设置时区，运行springboot+mybatis的服务器（我管它叫web服务器）也有一个时区，这2个时区如果不一致，似乎对查询结果会有差异
  • 只调整数据库的时区，查询结果会变吗?
  • 只调整web服务器的时区呢?
• springboot+mybatis，底层也是jdbc，jdbc的驱动连接mysql会需要一个时区，这个时区是读取数据库的。读出来的值，会不会根据web服务器的时区再转换一次?

上述问题有点复杂，待有空再研究。