こんにちは GANMA!でエンジニアをやっている平野です。この記事は FLINTERS10 周年記念として 133 日間ブログを書き続けるチャレンジの70日目の記事となります。

背景

皆さんは Java/Scala で DB に接続するプログラムを作成する場合ライブラリは何を利用するでしょうか？ScalikeJDBC, Slick, Anorm など色々なライブラリが存在しますが、いずれも内部的にmysql connector/j を使用しているかと思います。 mysql connector/j を使って時刻データを DB に読み書きしていると意図していたものと異なった時刻が書き込まれたり読み込まれたりすることがあります。そこでこの記事では mysql connector/j で時刻のずれなく DB への読み書きが行えるようになる方法を説明します。

問題点

時刻がずれるというのはどのようなことでしょうか？例えば以下のプログラムを考えてみます。

@main def hello: Unit =
  TimeZone.setDefault(TimeZone.getTimeZone("GMT+02:00"));
  println(ZoneId.systemDefault())
  val prop: Properties = new Properties();
  val conn = DriverManager.getConnection(
    "jdbc:mysql://localhost/sample?" +
      "user=root&password=root",
    prop
  );

  // mysql connector java
  val stmt: Statement = conn.createStatement()

  val dt = LocalDateTime.parse("2023-01-01T09:00:00")
  val ts = OffsetDateTime.parse("2023-01-01T00:00:00Z")
  val ps = conn.prepareStatement(
    "INSERT INTO person (name, age, dt, ts) VALUES ('John Doe', 33, ?, ?)"
  )
  ps.setObject(1, dt)
  ps.setObject(2, ts)
  ps.executeUpdate()

なおサーバーのタイムゾーンは UTC+1 とします。

> SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;
# @@GLOBAL.time_zone, @@SESSION.time_zone
'+01:00', '+01:00'

person テーブルの dt と ts の型はそれぞれ datetime 型と timestamp 型です。

desc person;
# Field, Type, Null, Key, Default, Extra
'id', 'bigint', 'NO', 'PRI', NULL, 'auto_increment'
'name', 'text', 'NO', '', NULL, ''
'age', 'bigint', 'YES', '', NULL, ''
'dt', 'datetime', 'NO', '', NULL, 'DEFAULT_GENERATED on update CURRENT_TIMESTAMP'
'ts', 'timestamp', 'NO', '', 'CURRENT_TIMESTAMP', 'DEFAULT_GENERATED on update CURRENT_TIMESTAMP'

このプログラムを実行したときに保存されるレコードの ts カラムの値はどうなるでしょうか？保存しようとしている時刻は 2023-01-01T00:00:00Z でこれは UTC+1 のタイムゾーンでは 2023-01-01T01:00:00 の時刻になります。サーバーのタイムゾーンは UTC+1 なので保存される値は 2023-01-01T01:00:00 になるはずです。ところが実際に DB にクエリしてみると ts の値は 2023-01-01T02:00:00 になります。

SELECT * FROM sample.person;
# id, name, age, dt, ts
'53', 'John Doe', '33', '2023-01-01 09:00:00', '2023-01-01 02:00:00'

保存しようとした時刻と 1 時間ずれた時刻が DB に保存されてしまいました。

時刻がずれる原因

mysql connector/j は特に何も設定しないと server のタイムゾーンを local(JVM)のタイムゾーンと同じと認識します。上記の例では local のタイムゾーンが UTC+2 に設定されているので server のタイムゾーンも UTC+2 として認識します。結果として 2023-01-01T00:00:00Z の UTC+2 における時刻である 2023-01-01T02:00:00 の値が DB に保存されます。

connector/j で時刻をずらさずに保存する方法

connector/j で時刻をずらさずに保存するためには preserveInstants と connectionTimeZone プロパティを使用します。 preserveInstants プロパティは時刻のずれが起きないようにタイムゾーンによる変換処理を行うかどうかを設定します。true にするとタイムゾーンによる変換が行われるようになります。connectionTimeZone プロパティはサーバーのセッションタイムゾーンを指定するために使われます。デフォルトで LOCAL に設定されており、server のタイムゾーンが local(JVM)のタイムゾーンと同じと認識されます。よって　 preserveInstants プロパティを true, connectionTimeZone を SERVER に設定することでずれを防ぐことができます。

@main def hello: Unit =
  TimeZone.setDefault(TimeZone.getTimeZone("GMT+02:00"));
  println(ZoneId.systemDefault())
  val prop: Properties = new Properties();
  prop.setProperty("preserveInstants", "true") // 追加
  prop.setProperty("connectionTimeZone", "SERVER") // 追加
  val conn = DriverManager.getConnection(
    "jdbc:mysql://localhost/sample?" +
      "user=root&password=root",
    prop
  );
  
  // mysql connector java
  val stmt: Statement = conn.createStatement()

  val dt = LocalDateTime.parse("2023-01-01T09:00:00")
  val ts = OffsetDateTime.parse("2023-01-01T00:00:00Z")
  val ps = conn.prepareStatement(
    "INSERT INTO person (name, age, dt, ts) VALUES ('John Doe', 33, ?, ?)"
  )
  ps.setObject(1, dt)
  ps.setObject(2, ts)
  ps.executeUpdate()

上記プログラムを実行すると ts の値として 2023-01-01 01:00:00 が保存されます。上記プログラムでは 2023-01-01 02:00:00 という時刻が 2023-01-01 01:00:00 に変換(UTC+2 から UTC+1 への変換)が行われたあとに DB サーバーへ送られます。また逆に取得する時には DB サーバーから送られてきた 2023-01-01 01:00:00 という時刻を 2023-01-01 02:00:00 に変換(UTC+1 から UTC+2 への変換)します。

  @main def hello: Unit =
  TimeZone.setDefault(TimeZone.getTimeZone("GMT+02:00"));
  val prop: Properties = new Properties();
  prop.setProperty("preserveInstants", "true")
  prop.setProperty("connectionTimeZone", "SERVER")
  val conn = DriverManager.getConnection(
    "jdbc:mysql://localhost/sample?" +
      "user=root&password=root",
    prop
  );

  // mysql connector java
  val stmt: Statement = conn.createStatement()

  val dt = LocalDateTime.parse("2023-01-01T09:00:00")
  val ts = OffsetDateTime.parse("2023-01-01T00:00:00Z")
  val ps = conn.prepareStatement(
    "INSERT INTO person (name, age, dt, ts) VALUES ('John Doe', 33, ?, ?)"
  )
  ps.setObject(1, dt)
  ps.setObject(2, ts)
  ps.executeUpdate()

  val rs: ResultSet = stmt.executeQuery("SELECT dt, ts FROM person")
  rs.next()
  println(rs.getObject("dt", classOf[LocalDateTime]))
  println(rs.getObject("ts", classOf[OffsetDateTime]))
  println(rs.getTimestamp("ts"))

出力結果

2023-01-01T09:00
2023-01-01T01:00+01:00
2023-01-01 02:00:00.0

まとめ

preserveInstants と connectionTimezone プロパティを使うことで mysql connector/j を使っているときにずれなく時刻を DB へ読み書きする方法を説明しました。

本記事ではmysql connector/jを直接使用する場合のみ説明しましたがScalikeJDBC, Slickなど内部的にmysql connector/jを利用しているライブラリでもpreserveInstantsプロパティの設定変更は効果があると思われます。

また、preserveInstants はデフォルトで true になっているのですが connectionTimeZone は LOCAL に設定されています(おそらく旧バージョンとの互換性維持のため)。もし互換性等気にする必要がなければ、これらの設定を行い意図せず時刻のずれが発生することを防いでみてはいかがでしょうか？