MySQLのTIMESTAMP値をJVMのタイムゾーンに合わせて取得する方法

「MySQLサーバのデフォルトタイムゾーン」と「Javaアプリケーションのタイムゾーン」が異なる（例：MySQLサーバがUTCでアプリケーション側がJSTなど）場合、MySQLの TIMESTAMP型 に設定されている値を何の考慮もせずに取得すると・・・意図しない日時なってしまうことがあります。

前提

本エントリーでは、特に断りがない場合は、

• MySQLサーバのデフォルトタイムゾーンはUTC
• JavaアプリケーションのタイムゾーンはJST(Asia/Tokyo)

という前提で記載します

意図しない日時となる具体例

意図しない日時となる具体例を見ていきましょう。以下のようなテーブルがあったとします。※TIMESTAMP型とDATETIME型の挙動の違いを示すためにDATETIME型も定義しておきます。

CREATE TABLE DATE_TEST (
    item_timestamp TIMESTAMP(3),
    item_datetime DATETIME(3)
);

このテーブルに、以下のSQLを実行してCURRENT_TIMESTAMPの値を入れます。

INSERT INTO DATE_TEST VALUES(CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

ここでは、上記SQLを実行するセッションのタイムゾーンはUTCとします。すると・・・TIMESTAMP型およびDATETIME型のカラムには同じ日時を示す値が設定されます。例えば「2021-08-07T06:50:48.679Z」とします。

これを、JST(Asia/Tokyo)で動くJavaアプリケーションで取得するとどうなるかというと・・・以下のようになります（本エントリーではSpringのJdbcTemplateというクラスを使用した結果になります）。

java.time.LocalDateTime  : 2021-08-07T06:50:48.679
java.time.ZonedDateTime  : 2021-08-07T06:50:48.679+09:00[Asia/Tokyo]
java.time.OffsetDateTime : 2021-08-07T06:50:48.679+09:00
java.sql.Timestamp       : 2021-08-07 06:50:48.679

なんかおかしいですよね・・・ サーバに保存されている日時がUTC基準なので、その時刻をJSTで表現するなら「2021-08-07T15:50:48.679」になっていないと、異なる日時を示していることになってしまう。例えばOffsetDateTimeが「2021-08-07T06:50:48.679+09:00」になっているけど、これってUTCで表現すると「2021-08-06T21:50:48.679Z」なので・・・9時間時間が巻き戻ってしまいます

どうすればズレないようにすることができるの？

いろいろやり方はある（関数使ったり、アプリケーション側でタイムゾーン意識した値に変換したり・・）とは思いますが、JDBCドライバのオプションでセッションのタイムゾーンを指定する方法が良いのかな〜と思っています。

以下で紹介するオプションを利用すると、MySQLのコマンドライン上で、

set time_zone = 'Asia/Tokyo';

を実行するのと同義のようです。

MySQL Connector/J(JDBC Driver)

MySQL Connector/Jを使う場合は、8.0.23より追加されたオプション「forceConnectionTimeZoneToSession」を使うと「connectionTimeZone」オプションで指定したタイムゾーンに変換してくれます。「connectionTimeZone」オプションのデフォルトは「LOCAL(=JVMのタイムゾーン)」になります。

jdbc:mysql://localhost:3306/devdb?forceConnectionTimeZoneToSession=true

詳しい内容は リファレンス を参照してください。

MariaDB Connector/J(JDBC Driver)

MariaDB Connector/Jを使う場合は、「sessionVariables」オプションを使用すると「time_zone」に指定したタイムゾーンに変換してくれます。

jdbc:mysql://localhost:3306/devdb?sessionVariables=time_zone='Asia/Tokyo'

詳しい説明はないようですが、念のため リファレンス のリンクを貼っておきます。

DATETIME型だとどうなる？

私も詳しいところはわかっていませんが結論だけ記載すると・・・本エントリーで紹介したパラメータを指定しても、動作は変わりません。　例えば「2021-08-07T07:24:46.658Z」 でDBにINSERTしたものを検索すると・・・

TIMESTAMP型から取得した値は、

java.time.LocalDateTime  : 2021-08-07T16:24:46.658
java.time.ZonedDateTime  : 2021-08-07T16:24:46.658+09:00[Asia/Tokyo]
java.time.OffsetDateTime : 2021-08-07T16:24:46.658+09:00
java.sql.Timestamp       : 2021-08-07 16:24:46.658

という感じで、+9:00された値になりますが、DATETIME型から取得した値は、

java.time.LocalDateTime  : 2021-08-07T07:24:46.658
java.time.ZonedDateTime  : 2021-08-07T07:24:46.658+09:00[Asia/Tokyo]
java.time.OffsetDateTime : 2021-08-07T07:24:46.658+09:00
java.sql.Timestamp       : 2021-08-07 07:24:46.658

となってしまいます。

まとめ

タイムゾーンは合わせましょう！！　ま〜パブリッククラウドのマネージドサービスを使うとデフォはUTCだよ〜だとは思いますが（今参画している案件がまさにこの状態w）
そして・・・タイムゾーンも難しいな〜

検証バージョン

動作検証に使った各種プロダクトのバージョンは以下のとおりです。

• MySQL 5.7 (on Testcontainers 1.5.3)
• MySQL Connector/J 8.0.26
• MariaDB Connector/J 2.7.3
• Spring Boot 2.5.3 (Spring Framework 5.3.9)

検証コード

• https://github.com/kazuki43zoo/mysql-time-zone