time_zone参数使用详解

背景

time_zone参数是MySQL记录时区信息的变量,经常有客户发起对time_zone的修改需求,原因包括但不限于如下几点:

  • 新迁移上云的RDS实例,修改time_zone对齐原数据库实例的时区信息
  • 由于JDBC时区函数的问题,导致应用程序查看到的时间和实际时间存在若干小时的偏差

问题现象

如下案例,MySQL实际存储的timestamp字段的时间比程序访问看到的时间点差了13个小时:

  • RDS实例参数设置如下:
    image.png
  • 使用MySQL客户端连接,查看时间数据如下:
    image.png
  • 使用JDBC访问数据库,查看时间数据如下:
    image.png

原因分析

当前数据库实例的时区参数time_zone设置取值是system,表示继承系统时区参数system_time_zone的取值CST。由于JDBC负责配置时区的函数对CST时区解析存在混淆,导致应用程序获取的是古巴标准时:

  • CST(China Standard Time)中国标准时,UTC +08:00
  • CST(Cuba Standard Time)古巴标准时,UTC -05:00

解决方案

设置time_zone参数,明确指定数据库实例的时区,不使用引发误解的CST时区。

案例复现

创建测试表

CREATE TABLE tb_01 (
   id int(11) NOT NULL AUTO_INCREMENT,
   dtime datetime NOT NULL,
   stamp timestamp NOT NULL,
   PRIMARY KEY (id)
) ENGINE=InnoDB;

测试过程

设置time_zone并插入数据 设置time_zone并读取数据 输出结果 datetime字段时间差 timestamp字段时间差
set session time_zone='system';
insert into tb_01(id,dtime,stamp)
values(1,'2018-12-24 12:00:00','2018-12-24 12:00:00');
set session time_zone='system'; id=1
dtime='2018-12-24 12:00:00'
stamp='2018-12-24 12:00:00'
不变 不变
set session time_zone='+8:00'; id=1
dtime='2018-12-24 12:00:00'
stamp='2018-12-24 12:00:00'
不变 不变
set session time_zone='-6:00'; id=1
dtime='2018-12-24 12:00:00'
stamp='2018-12-23 22:00:00'
不变 差14小时
set session time_zone='-5:00'; id=1
dtime='2018-12-24 12:00:00'
stamp='2018-12-23 23:00:00'
不变 差13小时
set session time_zone='+9:00'; id=1
dtime='2018-12-24 12:00:00'
stamp='2018-12-24 13:00:00'
不变 差1小时
set session time_zone='-6:00';
insert into tb_01(id,dtime,stamp)
values(2,'2018-12-24 12:00:00','2018-12-24 12:00:00');
set session time_zone='system'; id=2
dtime='2018-12-24 12:00:00'
stamp='2018-12-25 02:00:00'
不变 差14小时
set session time_zone='+8:00'; id=2
dtime='2018-12-24 12:00:00'
stamp='2018-12-25 02:00:00'
不变 差14小时
set session time_zone='-6:00'; id=2
dtime='2018-12-24 12:00:00'
stamp='2018-12-24 12:00:00'
不变 不变
set session time_zone='-5:00'; id=2
dtime='2018-12-24 12:00:00'
stamp='2018-12-24 13:00:00'
不变 差1小时
set session time_zone='+9:00'; id=2
dtime='2018-12-24 12:00:00'
stamp='2018-12-25 03:00:00'
不变 差15小时

总结建议

总结

  • time_zone参数在数据插入和数据读取时的取值需要对齐,否则对于timestamp类型字段会导致时区差异,而datetime类型字段不受影响
  • 数据插入到数据库后,不论如何调整time_zone参数设置,timestamp类型字段的数据内容都不会变化(checksum table结果一致),只是读取显示存在时区偏移
  • 产生以上现象的原因是因为MySQL写入时把timestamp字段按照当前时区设置转换后存储,读取时按照当前时区设置转换后展现。

建议

建议仅在新建RDS实例时对time_zone参数进行修改,在RDS实例使用过程中不建议修改,否则插入和读取的timestamp类型数据由于前后时区设置不一致,会造成数据错乱问题。