MySQL存储timestamp类型值的时区转换问题

季鱼豆腐汤哎
424 阅读4分钟

开发中应用服务器产生的当前时间，保存到数据库timestamp列,发现timpstamp的值并不正确。Why Why Why?（菜鸡呐喊~）下面理解下这个现象的产生过程以及解决方法。

一、问题描述

场景： 假设一个应用程序正在某个应用程序服务器上运行，并使用 Connector/J 连接到 MySQL 服务器。某些事件发生在连接会话中，为其生成时间，且事件时间与应用程序服务器的 JVM 时区相关。 上面提到的时间字段设计的MySQL字段类型是timestamp，这些时间将被存储到 MySQL 服务器上，并且稍后也会从MySQL中 检索。

  • Connector/J： MySQL的JDBC编程的驱动，也就是maven坐标groupId为mysql，artifactId为mysql-connector-java的依赖包。

  • 这里举例说明一下“某些事件发生在连接会话中，为其生成时间，并且事件时间与应用程序服务器的 JVM 时区相关”：应用程序收到前端更新某条记录的请求，为了标记更新操作的时间，生成了一个当前时间，当前时间参考的时区就是JVM时区，最终这条发生变更记录和和更新时间，通过Connector/J创建的会话，将数据保存到MySQL服务器中。可以结合“图1.应用、会话、MySQL服务器时区关系示意图”理解。

  • timestamp：时间戳，当期时间（UTC）和1970-01-01 00:00:00（UTC）的差值，此差值为毫秒数。

应用、会话、MySQL服务器时区关系示意图.png

图1.应用、会话、MySQL服务器时区关系示意图


问题： 假设当前JVM时区与会话时区不相同，时间是如何从应用程序最终保存到MySQL服务器的呢？
如果当前JVM时区与会话时区不相同，并且没有采取任何手段处理差异时,Connector/J会将JVM时区上生成的时间原样发送给MySQL服务器，不对时间做任何时区的转化。MySQL服务器在收到时间数据之后、存储timestamp类型的值之前，会将传递给MySQL服务器的时间从会话时区转化为UTC时区时间再存储。
这时候问题就出现了：存储的timestamp列的值并不是正确的时间，因为它忽视了JVM时区和会话时区之间的差异。下面可以结合“图2.timestamp类型值的存储和读取的时区转换示意图”中具体的例子理解这个问题产生的过程。

timestamp类型值的存储和读取的时区转换示例.png

二、解决方法

timestamp列存储和读取的值不正确，根本原因是在JVM时区和会话时区不相等的情况下，没有采取任何手段处理两者的差异。 有两大类解决方案：

  1. 让JVM时区、MySQL会话时区保持一致

  2. Connector/J获取JVM时区和MySQL会话时区的差异，并对时间进行时区转换。 这种解决方案主要是通过Connector/J的配置属性preserveInstants、connectionTimeZone、forceConnectionTimeZoneToSession实现的。

  • preserveInstants={true|false}，默认为true

    • 当preserveInstants设置为true时，Connector/J会去获取connectionTimeZone属性值，在进行timestamp类型值的存储之前，针对JVM时区和会话时区时的差异进行转换。
    • 当preserveInstants设置为false时，应用程序产生的时间将会原样存储到MySQL服务器中，不会试图在JVM时区和会话时区之间进行转换。

  • connectionTimeZone={LOCAL|SERVER|用户指定时区}

    • connectionTimeZone设置为LOCAL
      设置MySQL会话时区为JVM时区，Connector/J原样传递时间，MySQL服务器在存储前，将时间从会话时区（JVM时区）转换到UTC。

    • connectionTimeZone设置为SERVER
      Connector/J查询MySQL会话时区，如果会话时区与JVM时区不同，Connector/J在传输时间之前会将时间在JVM时区和会话时区之间进行转换。MySQL服务器在存储前，将时间从会话时区转换到UTC。

    • connectionTimeZone设置为用户指定时区
      设置MySQL会话时区为用户指定时区，如果会话时区与JVM时区不同，Connector/J在传输时间之前会将时间在JVM时区和会话时区（用户指定时区）之间进行转换。MySQL服务器在存储前，将时间从会话时区（用户指定时区）转换到UTC

  • forceConnectionTimeZoneToSession={true|false}，默认为false

    • forceConnectionTimeZoneToSession=true，connectionTimeZone=LOCAL/用户指定时区时，会将MySQL会话时区修改为JVM时区/用户指定时区。

对于 Connector/J 8.0.23 及更高版本，serverTimezone 是 connectionTimeZone 的别名。 对于 Connector/J 8.0.22 及更早版本，serverTimezone 用于覆盖服务器上的会话时区设置。

三、总结

测试验证中...请勿轻信，谨防上当受骗~

解决方案操作/配置
保证JVM时区和MySQL会话时区保持一致修改JVM时区或者MySQL时区，使两者保持一致
指定会话时区a）preserveInstants=true&connectionTimeZone=SERVER
b）connectionTimeZone=LOCAL& forceConnectionTimeZoneToSession=true
c）preserveInstants=true&connectionTimeZone=user-defined-time-zone& forceConnectionTimeZoneToSession=true

官方文档是最具有参考价值的，这里附上官方文档链接供参考：dev.mysql.com/doc/connect…

avatar
文章
阅读
粉丝