mysql的时区问题

mysql的时区问题

查看及设置时区

查看时区

命令：

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone |        |
| time_zone        | SYSTEM |
+------------------+--------+
1
2
3
4
5
6
7

返回：

system_time_zone：mysql启动的时候，mysql服务所在服务器（主机）的时区，windows系统返回为空，linux系统一般会返回CST，个人认为，这里的CST表示的是中国标准时间（CST还可以表示其它某些地区的标准时间），有时可能会返回UTC（协调世界时），粗略理解为0时区时间（GMT，格林尼治标准时间），个人认为，system_time_zone并不是很重要

time_zone：当前会话使用的时区，可以在配置文件中指定默认值，如果不指定，默认值是SYSTEM，表示使用system_time_zone指向的时区，time_zone可以使用时调整，一些函数（比如：now()）返回的值就和time_zone有关，但一般不要轻易改变

配置文件设置time_zone默认值

在my.ini文件（linux系统是my.cnf文件）的[mysqld]节点设置，设置后重启服务

[mysqld]
default-time-zone = '+0:00'
1
2

命令设置时区

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

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone |        |
| time_zone        | +08:00 |
+------------------+--------+
2 rows in set (0.04 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

datetime和timestamp的区别

结论：datetime可以理解为保存的是一个字符串，切换时区后，不会有变化，timestamp可以理解为保存的是距离1970-01-01 08:00:00的时间差（将当前时区的时间转换成0时区的时间，然后计算与1970-01-01 08:00:00的时间差），切换时区后，显示的是当前时区所对应的时间

创建一个表

CREATE TABLE `time_zone_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `create_time` datetime NOT NULL,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4
5
6

插入数据并根据在不同时区查看数据

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone |        |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.03 sec)

mysql> INSERT INTO `test`.`time_zone_test` (`create_time`, `update_time`) VALUES ('2022-09-03 23:01:14', '2022-09-03 23:01:14');
Query OK, 1 row affected (0.01 sec)

mysql> select * from time_zone_test;
+----+---------------------+---------------------+
| id | create_time         | update_time         |
+----+---------------------+---------------------+
|  1 | 2022-09-03 23:01:14 | 2022-09-03 23:01:14 |
+----+---------------------+---------------------+
1 row in set (0.04 sec)

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

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

mysql> select * from time_zone_test;
+----+---------------------+---------------------+
| id | create_time         | update_time         |
+----+---------------------+---------------------+
|  1 | 2022-09-03 23:01:14 | 2022-09-03 23:01:14 |
+----+---------------------+---------------------+
1 row in set (0.06 sec)

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

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

mysql> select * from time_zone_test;
+----+---------------------+---------------------+
| id | create_time         | update_time         |
+----+---------------------+---------------------+
|  1 | 2022-09-03 23:01:14 | 2022-09-03 10:01:14 |
+----+---------------------+---------------------+
1 row in set (0.04 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

可以发现

• create_time的类型是datetime，切换时区后，显示的时间不变，update_time的类型是timestamp，切换时区后，显示的时间有变化
• 同时可以发现，我的电脑默认的时区就是 +08:00

mybatis连接mysql的问题

java程序，以springboot程序为例，使用mybatis操作mysql时，会涉及到时区转换的问题

连接mysql并设置时区

spring:
  datasource:
    url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=GMT%2B8
    username: xxxx
    password: xxxx
    driver-class-name: com.mysql.cj.jdbc.Driver
1
2
3
4
5
6

serverTimezone表示当前会话的时区，这句话应该是错误的，但是现在大部分文章都这样写

serverTimezone表示的是希望用户配置一个与mysql服务器默认的time_zone相同的值，我们一般设置为serverTimezone=GMT%2B8或serverTimezone=Asia/Shanghai，因为我们的mysql服务器time_zone一般就是+08:00

serverTimezone的作用是将程序的时区与serverTimezone设置的时区做比较，然后将时区转换后的时间字符串发送给mysql服务器，到mysql服务器上，使用的会话时区就是默认的会话时区，就是默认查询出来的time_zone的值

修改windows系统的时区（附加）

使用tzutil工具

C:\Users\yangs>tzutil /g
China Standard Time
C:\Users\yangs>tzutil /s "Greenwich Standard Time"

C:\Users\yangs>tzutil /g
Greenwich Standard Time
1
2
3
4
5
6

添加数据时，程序时区、serverTimezone时区不一致导致的时间转换问题

实际这里涉及到三个时区

• 程序时区：springboot的时区
• serverTimezone设置的时区
• mysql服务默认的时区time_zone

springboot程序默认使用所在系统的时区

例1

springboot项目所在操作系统的时区为：(UTC+08:00) 北京，重庆，香港特别行政区，乌鲁木齐

连接mysql设置serverTimezone=GMT%2B9，(UTC+09:00) 大阪，札幌，东京

当前时间：

• (UTC+08:00) 北京 10:30（电脑时间）

• (UTC+09:00) 大阪 11:30

mysql的默认时区：+08:00

代码逻辑：获取当前时间并存入到数据库（createTime、updateTime）

程序的入参（查看打印的日志）：

JDBC Connection [HikariProxyConnection@886416306 wrapping com.mysql.cj.jdbc.ConnectionImpl@25baed04] will not be managed by Spring
==>  Preparing: insert into time_zone_test (id, create_time, update_time ) values (?, ?, ? ) 
==> Parameters: null, 2022-09-04 10:22:33.937(Timestamp), 2022-09-04 10:22:33.937(Timestamp)
<==    Updates: 1
1
2
3
4

日志打印的是当前时间，但是这个并不是发给mysql的时间，发给mysql的时间会做时区转换

发给mysql的时间：

可在org.apache.ibatis.executor.SimpleExecutor#doUpdate查看

HikariProxyPreparedStatement@1978182025 wrapping com.mysql.cj.jdbc.ClientPreparedStatement: insert into time_zone_test (id, create_time, update_time
      )
    values (null, '2022-09-04 11:22:33.937', '2022-09-04 11:22:33.937'
      )
1
2
3
4

数据库中查看：

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

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

mysql> select * from time_zone_test where id = 8;
+----+---------------------+---------------------+
| id | create_time         | update_time         |
+----+---------------------+---------------------+
|  8 | 2022-09-04 11:22:34 | 2022-09-04 11:22:34 |
+----+---------------------+---------------------+
1 row in set (0.04 sec)

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

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

mysql> select * from time_zone_test where id = 8;
+----+---------------------+---------------------+
| id | create_time         | update_time         |
+----+---------------------+---------------------+
|  8 | 2022-09-04 11:22:34 | 2022-09-04 12:22:34 |
+----+---------------------+---------------------+
1 row in set (0.04 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

由此可以看出：

1. springboot程序是+08:00，serverTimezone设置的是+09:00，所以在组装sql语句的时候会给时间+1
2. 执行sql语句时，就是在mysql服务器默认时区(+08:00)进行的，可以根据update_time的值判断，如果是在+09:00执行的，那么在+08:00查询出来的时间就会少1小时，实际上并没有
3. 从2也能看出，serverTimezone设置的值并不是当前会话的时区，只是告诉程序应该怎么转换这个时间

例2

springboot项目所在操作系统的时区为：(UTC+08:00) 北京，重庆，香港特别行政区，乌鲁木齐

连接mysql设置serverTimezone=GMT%2B9，(UTC+09:00) 大阪，札幌，东京

当前时间：

• (UTC+08:00) 北京 16:40（电脑时间）

• (UTC+09:00) 大阪 17:40

mysql的默认时区：+08:00

代码逻辑：使用now()函数存入到数据库（createTime、updateTime）

发给mysql的语句：

HikariProxyPreparedStatement@764739765 wrapping com.mysql.cj.jdbc.ClientPreparedStatement: insert into time_zone_test (id, create_time, update_time
      )
    values (null, now(), now()
      )
1
2
3
4

数据库中查看：

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone |        |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.05 sec)

mysql> select * from time_zone_test where id = 15;
+----+---------------------+---------------------+
| id | create_time         | update_time         |
+----+---------------------+---------------------+
| 15 | 2022-09-04 16:45:30 | 2022-09-04 16:45:30 |
+----+---------------------+---------------------+
1 row in set (0.05 sec)

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

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

mysql> select * from time_zone_test where id = 15;
+----+---------------------+---------------------+
| id | create_time         | update_time         |
+----+---------------------+---------------------+
| 15 | 2022-09-04 16:45:30 | 2022-09-04 16:45:30 |
+----+---------------------+---------------------+
1 row in set (0.07 sec)

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

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

mysql> select * from time_zone_test where id = 15;
+----+---------------------+---------------------+
| id | create_time         | update_time         |
+----+---------------------+---------------------+
| 15 | 2022-09-04 16:45:30 | 2022-09-04 17:45:30 |
+----+---------------------+---------------------+
1 row in set (0.08 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

由此可以看出：

1. now()函数是在+08:00时区执行的，与serverTimezone设置的值无关

例3

springboot项目所在操作系统的时区为：(UTC+00:00) 蒙罗维亚，雷克雅未克

连接mysql设置serverTimezone=GMT%2B8，(UTC+08:00) 北京，重庆，香港特别行政区，乌鲁木齐

当前时间：

• (UTC+00:00) 蒙罗维亚 9:00（电脑时间）
• (UTC+08:00) 北京 17:00

mysql的默认时区：+00:00

代码逻辑：获取当前时间并存入到数据库（createTime、updateTime）

程序的入参（查看打印的日志）：

JDBC Connection [HikariProxyConnection@1876572082 wrapping com.mysql.cj.jdbc.ConnectionImpl@584e44f5] will not be managed by Spring
==>  Preparing: insert into time_zone_test (id, create_time, update_time ) values (?, ?, ? )
==> Parameters: null, 2022-09-04 09:10:41.157(Timestamp), 2022-09-04 09:10:41.157(Timestamp)
<==    Updates: 1
1
2
3
4

日志打印的是当前时间，但是这个并不是发给mysql的时间，发给mysql的时间会做时区转换

发给mysql的时间：

HikariProxyPreparedStatement@419867077 wrapping com.mysql.cj.jdbc.ClientPreparedStatement: insert into time_zone_test (id, create_time, update_time
      )
    values (null, '2022-09-04 17:10:41.157', '2022-09-04 17:10:41.157'
      )
1
2
3
4

数据库中查看：

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

mysql> select * from time_zone_test where id = 16;
+----+---------------------+---------------------+
| id | create_time         | update_time         |
+----+---------------------+---------------------+
| 16 | 2022-09-04 17:10:41 | 2022-09-04 17:10:41 |
+----+---------------------+---------------------+
1 row in set (0.05 sec)

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

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

mysql> select * from time_zone_test where id = 16;
+----+---------------------+---------------------+
| id | create_time         | update_time         |
+----+---------------------+---------------------+
| 16 | 2022-09-04 17:10:41 | 2022-09-05 01:10:41 |
+----+---------------------+---------------------+
1 row in set (0.09 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

由此可以看出：

1. springboot程序是+00:00，serverTimezone设置的是+08:00，所以在组装sql语句的时候会给时间+8
2. 执行sql语句时，在mysql服务器默认时区(+00:00)进行的，将发送过来的17点，在+00:00时区执行保存
3. 将时区调整为+08:00进行查询，会发现update_time时间又增加了8，这是正确的

例4

springboot项目所在操作系统的时区为：(UTC+00:00) 蒙罗维亚，雷克雅未克

连接mysql设置serverTimezone=GMT%2B8，(UTC+08:00) 北京，重庆，香港特别行政区，乌鲁木齐

当前时间：

• (UTC+00:00) 蒙罗维亚 9:30（电脑时间）
• (UTC+08:00) 北京 17:30

mysql的默认时区：+00:00

代码逻辑：使用now()函数存入到数据库（createTime、updateTime）

发给mysql的语句：

HikariProxyPreparedStatement@1333630381 wrapping com.mysql.cj.jdbc.ClientPreparedStatement: insert into time_zone_test (id, create_time, update_time
      )
    values (null, now(), now()
      )
1
2
3
4

数据库中查看：

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

mysql> select * from time_zone_test where id = 17;
+----+---------------------+---------------------+
| id | create_time         | update_time         |
+----+---------------------+---------------------+
| 17 | 2022-09-04 09:30:26 | 2022-09-04 09:30:26 |
+----+---------------------+---------------------+
1 row in set (0.06 sec)

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

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

mysql> select * from time_zone_test where id = 17;
+----+---------------------+---------------------+
| id | create_time         | update_time         |
+----+---------------------+---------------------+
| 17 | 2022-09-04 09:30:26 | 2022-09-04 17:30:26 |
+----+---------------------+---------------------+
1 row in set (0.06 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

由此可以看出：

1. now()函数是在+00:00时区执行的，与serverTimezone设置的值无关

查询数据时，程序时区、serverTimezone时区不一致导致的时间转换问题

查询数据时，也会对时间类型的数据进行转换

例5

环境与例4一致，进行查询操作

从mysql查询回来的数据（查看打印的日志）：

JDBC Connection [HikariProxyConnection@1625710104 wrapping com.mysql.cj.jdbc.ConnectionImpl@109c4ff6] will not be managed by Spring
==>  Preparing: select id, create_time, update_time from time_zone_test where id = ?
==> Parameters: 17(Integer)
<==    Columns: id, create_time, update_time
<==        Row: 17, 2022-09-04 09:30:26, 2022-09-04 09:30:26
<==      Total: 1
1
2
3
4
5
6

实际展示的数据（接口进行查询操作）

{
  "id": 17,
  "createTime": "2022-09-04T01:30:26.000+00:00",
  "updateTime": "2022-09-04T01:30:26.000+00:00"
}
1
2
3
4
5

由此可以看出：

1. mysql从+00:00时区查询出数据给到springboot程序
2. 由于设置了serverTimezone=GMT%2B8，程序会认为这是+08:00时区的数据，程序本身的时区是+00:00，所以转换成java对象数据时进行了-8操作，展示的数据也是-8后的结果
3. 添加数据时，是9:30 +00:00，查出来的数据是1:30 +00:00，这明显是不一致的，原因就在于serverTimezone设置的时区与mysql默认的时区不一致，其实使用now()函数，可以理解为保存的数据库中的数据是准确的。如果由程序生成时间，那么发送给mysql时会发生时间转换，此时保存到mysql中的数据就可以理解为是不准确的，即使查询展示的数据可能是对的

如果进行格式化，显示+08:00的时间

配置：

spring:
  jackson:
    time-zone: GMT+8   
1
2
3

显示：

{
  "id": 17,
  "createTime": "2022-09-04T09:30:26.000+08:00",
  "updateTime": "2022-09-04T09:30:26.000+08:00"
}
1
2
3
4
5

总结

1. 避免数据错误，serverTimezone设置的值一定要和mysql默认的时区一致（可以通过配置文件指定）
2. 不要轻易切换mysql的默认时区，如果程序不对应修改serverTimezone的值会造成数据错误
3. 建议mysql默认时区使用+08:00时区，datetime类型的数据实际可以理解为是字符串，是不变的，比如，保存数据时，使用的是+00:00时区，保存的就是+00:00时区的对应的值，虽然如果配置正确，程序可以转换成我们想要的+08:00时区的值，但是毕竟数据库中存储的就是当时+00：00时区的对应的值，所以还是建议mysql默认时区使用+08:00时区，储存的时间就是我们用的时间
4. 程序所在的时区应该可以任意，但是用户输入的时间类型的数据就需要特殊关注，比如，程序的时区是+00:00时区，用户输入的数据是+08:00时区数据，那就得明确告诉程序用户输入的是+08:00时区的数据。

如有错误，欢迎指正