java程序下jre的默认时区与mysql服务的时区不同,导致保存进数据库的时间有偏差分析

jre在运行时的默认时区

java.util.TimeZone.getDefault()

改变jre在运行时的默认时区方式有2种

• 通过指定jvm参数设置 user.timezone
  -Duser.timezone=UTC
• 设置操作系统环境变量 export TZ=UTC

mysql的jdbc驱动中的serverTimezone

这个参数用于指定 MySQL 服务器当前使用的时区，可以不指定，默认会从 MySQL服务器获取。

时区配置源码参考：
com.mysql.cj.protocol.a.NativeProtocol#configureTimezone

如果没有在jdbc驱动中设置时区,然后使用了mysql服务的时区
那么会在下面几个操作中使用这个时区

1. java.sql.Date、java.sql.Time、java.sql.Timestamp 的格式化后用于拼装 SQL

   com.mysql.cj.ClientPreparedQueryBindings#setTimestamp(int,
   java.sql.Timestamp, java.util.Calendar, int)
   com.mysql.cj.ClientPreparedQueryBindings#setDate(int, java.sql.Date,
   java.util.Calendar)
   com.mysql.cj.ClientPreparedQueryBindings#setTime(int, java.sql.Time,
   java.util.Calendar) ```
   

2. 将查询结果解析成 java.sql.Date、java.sql.Time、java.sql.Timestamp

   com.mysql.cj.jdbc.result.ResultSetImpl#getTimestamp(int,
   java.util.Calendar)
   com.mysql.cj.jdbc.result.ResultSetImpl#getTime(int,
   java.util.Calendar)
   com.mysql.cj.jdbc.result.ResultSetImpl#getDate(int,
   java.util.Calendar) ```
   
   
   
   

MySQL查询时区

show variables like '%time_zone%';

假定当前时间是 UTC 2020-04-01 00:00:00

set @@time_zone = '+08:00';
select current_timestamp();

set @@time_zone = '+08:00';
insert into test_table (id, create_timestamp) values (1, '2020-04-08 16:32:11');

会发现一个现象

• 如果 create_timestamp 数据类型是 TIMESTAMP 的话，则实际存储的值是 2020-04-08 08:32:11
（+08:00 --> UTC）看上面查看mysql的时区可知使用的是UTC时间
• 如果 create_timestamp 数据类型是 DATETIME 的话，则实际存储的值是 2020-04-08 16:32:11
（原样存储，不做任何转换）

那么试试+7呢,查询的数据还是上面插入的那条数据

set @@time_zone = '+07:00';
select create_timestamp from test_table where id=1;

• 如果 create_timestamp 数据类型是 TIMESTAMP 的话，则返回 2020-04-08 15:32:11 （UTC --> +07:00）
• 如果 create_timestamp 数据类型是 DATETIME 的话，则返回 2020-04-08 16:32:11
（原样返回，不做任何转换），此时就已经不能正确表示原始时间点了

到这里我们可以看到,如果mysql使用的是utc时间的话,会将保存进去的timestamp格式的时间转换成utc时间,在取出来的时候又会自动根据设置的time_zone对时间+对应的数值

• TIMESTAMP 在存储时，会将时间戳从当前时区(time_zone参数值)转换成 UTC 进行存储，在读取时，会将时间戳从 UTC
  时区转换为当前时区。
• 但是 DATE 和 DATETIME 在存取时并不会做上面的转换，而是将字面值直接存取。

日期相关的数据类型转换

从上面的时区转换过程可以看出，在 MySQL 的 JDBC 实现中，如果 serverTimezone 和 jre_default_timezone 不一致，那么 LocalDateTime 和 LocalDate 的查询结果就会和插入时不一样。如下图所示中就是jre_default_timezone = UTC,serverTimezone = + 7:00

所以在jdbc连接mysql的参数中可以指定serverTimezone与time_zone

jdbc:mysql://${spring.datasource.mosaic.host}:${spring.datasource.mosaic.port}/${spring.datasource.mosaic.database}?serverTimezone=UTC &useAffectedRows=true&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&connectTimeout=2000 &sessionVariables=character_set_connection=utf8mb4,character_set_client=utf8mb4,time_zone='%2b00%3a00'

这里的serverTimezone=UTC可以让时间值经过jdbc驱动的时候不作处理,直接当做utc时间传递给mysql 服务,然后mysql服务这里设置
time_zone='%2b00%3a00'也就是time_zone='+00:00',就会把java程序传递过来的值直接存进数据库