一文讲透MySQL driver读取时间时的时区处理

发表于 2022-06-20 分类于数据库阅读次数：本文字数： 2.9k 阅读时长 ≈ 10 分钟

理解连接属性 serverTimezone/connectionTimezone

在java中使用mysql client创建JDBC连接时可以指定以上两个连接参数。两者的功能是一致的，所以后面统一就用serverTimezone为例说明。官方文档描述如下所述。这个参数主要是告诉jdbc，数据库处理timestamp是按照什么时区处理的。jdbc第一次从数据库读取到的原始的年、月、日、时分秒都是按照server timezone处理好的。知道了server timezone之后，jdbc可以根据用户配置的serverTimezone的值，逆将这个年月日转换成UTC时间的毫秒数，最终jdbc可以根据自己本地jvm的时区以及这个逆向得到的UTC时间，得到正确的本地时间。

1	Specifies how the server's session time zone (in reference to which the timestamps are saved onto the server) is to be determined by Connector/J.

以上理解非常重要，理解了serverTimezone本质含义后，自然也就理解后续jdbc处理时区转换的原理了。下面分别介绍下jdbc读写数据库时，是如何配合serverTimezone做时区转换的。

读取时间字段

jdbc读取数据时区转换的基本步骤

数据库毫秒数存储
按照数据库时区转换成年月日
client根据使用不同的方法进行不同的时间处理

rs.getString()的底层原理

JDBC如果使用rs.getString，则不会对数据库上读取出来的时间做任何转换，直接输出读取到的年月日信息。使用的value处理器是StringValueFactory，这个value factory不依赖时区，直接按照读取到的年月日信息来组装输出结果。可以参考下面的堆栈了解具体调用链。

public String createFromTimestamp(InternalTimestamp its) {
     return String.format("%s %s", createFromDate(its),
             createFromTime(new InternalTime(its.getHours(), its.getMinutes(), its.getSeconds(), its.getNanos(), its.getScale())));
 }

rs.getTimestamp的底层原理

源码可能比较冗杂，但是getTimestamp简单总结读取的时区转换逻辑其实还是比较简单的：

client从数据库读取到的时间是数据库按照时区转换后的年月日信息，因此根据配置的JDBC属性serverTimezone先将时间逆向处理成UTC时间的毫秒数，用来初始化得到一个Timestamp实例对象
Timestamp实例对象toString输出时，使用jvm本身的local timezone(默认会取本机系统时区)的时区转成最终的本地时间

例如有个时间为1970-00-00 00:00:00，serverTimezone为+9，本机为+8。那么数据库读取出来的值1970-00-00 09:00:00根据serverTimezone转换成毫秒数为0，然后由于本地时区为+8，再加8个小时，也就得到最终本地的输出时间1970-00-00 08:00:00

当我们使用rs.getTimestamp的时候则使用SqlTimestampValueFactory处理时间，SqlTimestampValueFactory中关键的时间value处理如下:

cal是Calendar的实现类，是SqlTimestampValueFactory中的重要成员变量。处理value的时候会调用getTimeInMillis()方法中的updateTime()方法会根据JDBC设置的serverTimezone属性值将年月日转成该时区相当于0时区的毫秒数。然后使用这个值初始化Timestamp对象。例如severTimezone是+9，我们从数据库查询出来的毫秒数需要减少9小时的毫秒数，才是数据库底层真正存储的UTC时间
Timestamp对象是时区相关的，toString会按照local timezone再最终生成本地展示的时间

tips: 以上说的比较细，但是核心逻辑还是比较简单的，就是先将读取到的年月日信息转成server timezone对应的毫秒数来初始化Timestamp对象。

getString和getTimestamp读取时间处理总结

读取时间的测试

读取测试环境说明

测试使用的表结构如下，后续读写的测试都会使用这张表结构：

create table ws1.test_timezone
(
    id int(11) primary key,
    t  timestamp,
    d datetime
);

通过以下代码我们可以来加深之前原理的理解，以下代码在笔者本机mac上运行，代码中指定的本地时区和serverTimezone在后续测试中可以自行调整。

package com.clougence.wanshao.example.mysql;

import com.alibaba.druid.pool.DruidDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
import lombok.SneakyThrows;

/**
 * @author wanshao create time is  2021/8/17
 **/
public class TestConnection {

  @SneakyThrows
  public static void main(String[] args) {
    TimeZone.setDefault(TimeZone.getTimeZone("Asia/Shanghai"));
    Locale.setDefault(Locale.CHINESE);  
      
    String jdbcUrl = "jdbc:mysql://rm-xxxxx.mysql.rds.aliyuncs.com/ws1?serverTimezone=UTC";
    //
    DruidDataSource ds = new DruidDataSource();
    ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
    ds.setUrl(jdbcUrl);
    ds.setUsername("wanshao");
    ds.setPassword("xxxxx");
    Properties prop = new Properties();
    prop.put("useUnicode", "true");
    ds.setConnectProperties(prop);
    ds.init();
    try (Connection connection = ds.getConnection(); PreparedStatement ps = connection
        .prepareStatement("select * from ws1.test_timezone"); ResultSet rs = ps.executeQuery();) {
      if (rs.next()) {
        System.out.println("getTimestamp is " + rs.getTimestamp("t"));
        System.out.println("getString is " + rs.getString("d"));
      } else {
        throw new RuntimeException("can not get version for mysql ");
      }
    }
  }
}

读取测试结果

数据库读取到的年月日信息为：2022-06-19 22:29:34
结果可以看到，getString和本地时区、serverTimezone均无关，getTimestamp按照serverTimezone和本地时区转换得到最终结果

输出结果	本地时区	serverTimezone
getTimestamp is 2022-06-19 21:29:34.0
getString is 2022-06-19 22:29:34	+8	+9
getTimestamp is 2022-06-19 13:29:34.0
getString is 2022-06-19 22:29:34	+0	+9
getTimestamp is 2022-06-19 22:29:34.0
getString is 2022-06-19 22:29:34	+0	+0

写入时间字段

后续介绍写入原理时涉及参考示例，参考示例统一的时区配置如下：

本地时区： +8
client配置的serverTimezone属性: +9
数据库的时区: -5

不同写入方式测试时对以下代码做略微改动即可适配。

package com.clougence.wanshao.example.mysql;

import com.alibaba.druid.pool.DruidDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Locale;
import java.util.Properties;
import java.util.TimeZone;
import lombok.SneakyThrows;

/**
 * @author wanshao create time is  2021/8/17
 **/
public class TestConnection {

  @SneakyThrows
  public static void main(String[] args) {
    TimeZone.setDefault(TimeZone.getTimeZone("Asia/Shanghai"));
    Locale.setDefault(Locale.CHINESE);

    String jdbcUrl = "jdbc:mysql://rm-xxxx.mysql.rds.aliyuncs.com/ws1?serverTimezone=Japan";
    //
    DruidDataSource ds = new DruidDataSource();
    ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
    ds.setUrl(jdbcUrl);
    ds.setUsername("wanshao");
    ds.setPassword("xxxx");
    Properties prop = new Properties();
    prop.put("useUnicode", "true");
    ds.setConnectProperties(prop);
    ds.init();

    try (Connection connection = ds.getConnection(); PreparedStatement ps = connection
        .prepareStatement("select now()"); ResultSet rs = ps.executeQuery();) {
      while (rs.next()) {
        System.out.println("get Now result is(getTimestamp) " + rs.getTimestamp(1));
        System.out.println("get Now result is(getString) " + rs.getString(1));

      }
    }

    try (Connection connection = ds.getConnection(); PreparedStatement ps = connection
        .prepareStatement("insert into ws1.test_timezone(id,t,d) values (9,now(),now())");) {
        ps.execute();
    }

    try (Connection connection = ds.getConnection(); PreparedStatement ps = connection
        .prepareStatement("select * from ws1.test_timezone where id=9"); ResultSet rs = ps.executeQuery();) {
      while (rs.next()) {
        System.out.println("getTimestamp is " + rs.getTimestamp("t"));
        System.out.println("getString is " + rs.getString("d"));
      }
    }
  }
}

数据库server侧对于写入的处理

DB接收到的年月日字符串信息，都当成是自身数据库时区format的结果进行存储，与写入时client使用的参数都无关，因为根据mysql协议，都是按照文本将年、月日信息作为字符串数据发给数据库server的。

写入方式1：按照时间now()写入

insert语句执行now函数，数据库server端会获取数据库当前时区的format时间。客户端读取的时候如果使用getTimestamp则按照前文所说的方式获取处理后的结果，如果getString则获取数据库按照其自身时区format的原始的年月日信息。

1 2	### 参考sql insert into ws1.test_timezone(id,t,d) values (8,now(),now())"

例如上面的例子，数据库取到的本地时区时间format结果为2022-06-22 03:29:29
按照now()写入后，测试读取的结果为：

client端使用getString读取结果为：2022-06-22 03:29:29
client端使用getTimestamp读取结果为：2022-06-22 16:29:29

写入方式2：按照字符串写入

sql中指定具体时间的value，这个按照文本直接传递给数据库，数据库认为该年月日即是自身的时区的时间，然后转成毫秒数存储。

1	insert into ws1.test_timezone(id,t,d) values (8,'2022-06-22 03:29:29','2022-06-22 03:29:29')"

上面这个insert写入后，数据库将’2022-06-22 03:29:29’认为是数据库时区的一个时间进行存储。client测试读取的结果为：

client端使用getString读取结果为：2022-06-22 03:29:29
client端使用getTimestamp读取结果为：2022-06-22 16:29:29.0

now()函数和字符串写入总结

字符串或者函数写入的时间，其实都是按照文本传递给数据库server，数据库server直接当做是自己时区的时间进行存储。这个具体可以看driver写入时的源码，核心类是ClientPreparedStatement。执行ps.execute的调用链如下：

写入方式3：通过变量绑定写入

之前写入的sql我们做以下略微调整。按照ps.set方式去设置值。

按照ps.setString()处理

写入方式如下：

try (Connection connection = ds.getConnection(); PreparedStatement ps = connection
    .prepareStatement("insert into ws1.test_timezone(id,t,d) values (13,?,?)")) {
  ps.setString(1, "2022-06-22 03:29:29");
  ps.setString(2, "2022-06-22 03:29:29");
  ps.execute();
}

这种方式使用的类是ClientPreparedQueryBindings类中的setString方法，mysql client不会对文本做改动，直接赋值后发送给server，和之前字符串直接表示的写入效果是一样的。

client端使用getString读取结果为：2022-06-22 03:29:29
client端使用getTimestamp读取结果为：2022-06-22 16:29:29.0

按照ps.setTimestamp()处理

使用的代码示例如下：

try (Connection connection = ds.getConnection(); PreparedStatement ps = connection
    .prepareStatement("insert into ws1.test_timezone(id,t,d) values (15,?,?)")) {

  //本地东八区 2022-06-22 03:29:29对应的毫秒数
  Timestamp ts1 = new Timestamp(1655839769000L);
  Timestamp ts2 = new Timestamp(1655839769000L);

  ps.setTimestamp(1, ts1);
  ps.setTimestamp(2, ts2);
  ps.execute();
}

会调用ClientPreparedQueryBindings类中的setTimestamp方法，这里会获取配置的serverTimezone，我们是+9。传递的Timestamp对象，按照本地时区展示的时间应该是2022-06-22 03:29:29，在通过setTimestamp写入的时候，会根据指定的serverTimezone来调整时间。serverTimezone是+9，相比本地的时间需要多加1小时，因此client重新format后的结果是2022-06-22 04:29:29，最终sendPacket的时候发送的就是这个文本。

知道了数据库实际是以数据库时区的2022-06-22 04:29:29存储的，根据我们读取数据的原理，也就知道了获取到的数据为：

client端使用getString读取结果为：2022-06-22 04:29:29
client端使用getTimestamp读取结果为：2022-06-22 17:29:29.0

binlog中的时区

在binlog中datetime类型按照字符串存储、timestamp类型按照unix时间存储(无时区信息)。如果Java应用按照dump协议拉取binlog解析，解析出来的年月日信息取决于java应用TimeZone.getDefault()的值，也就是你的系统时区。

应用——数据同步中的跨时区同步

全量迁移

全量迁移一般是基于query sql下发给mysql driver将数据scan出来的。为了保证源对端的市区一致性，采用如下方式即可：

1 2	- 源端使用getString获取timestamp列的value - 对端写入时使用setString设置timestamp列的value

统一采用getString和setString按照字符串处理，则值的读写和本地时区以及jdbc的serverTimezone均无关

增量同步

MySQL增量同步主要是基于binlog解析来完成同步的。binlog中的timestamp类型是按照距离1970的时间戳存储的(小数点后的值单独存储)。以canal/cloudcanal为例，对timestamp类型的解析处理代码如下，将毫秒数直接转成本地local timezone的Timestamp对象。这样处理的话就直接改变了时区的值。例如源端数据库是-5时区，按照本地程序东八时区构造的timestamp对象，则会比原始数据库读取显示的值大13个小时。

解决方案：

解析的时候处理binlog中的时间戳时，format的时候需要按照源端数据库的时区进行format，然后对端写入的时候使用setString处理或者setObject(也是按照字符串写入)的进行处理即可。

总结

本文详细介绍了mysql driver读写时时区处理的各种情况，后续在涉及各种跨时区的时间处理问题上，都可以按照以上原理来判断时区的处理是否准确。