前回からの続き。JDBCでのデータ書き込み、読み出しの際のタイムゾーン周りを確認する。ややこしくて頭が爆発しそう。

前提条件

MySQL 5.7
- docker-composeでMySQL インスタンスをローカルで立ち上げる
MySQLのSystem time zoneはUTC+07:00 (Time zone = Asia/Bangkok) で固定
MySQL Connector/J 5.1 を利用

前提知識

JDBCでMySQLの DATETIME ないし TIMESTAMP 型に値を渡す場合、Java側は java.sql.Timestamp の値を用いる。
- java.sql.Timestamp はコンストラクタでUnix時間を受け取り、タイムゾーンの概念は持たない。
JDBCの接続文字列で指定可能なオプションの一つに useLegacyDatetimeCode があり、この値によって挙動が変わる。

実験

以下の2点のパラメータをいじりながら、データの書き込み、読み込みを観測する。書き込む値は前回と同じ、 '2021-02-02T12:00:00+00:00' とする。 java.sql.Timestamp に変換する際は、 java.text.SimpleDateFormat をUTCで用いる。

JVMのデフォルトのタイムゾーン。 TimeZone.setDefault() で変更する。
JDBCのパラメータ useLegacyDatetimeCode を変更してみる。

結果

MySQL側に保存する時刻のタイムゾーンを統一するときは useLegacyDatetimeCode=false を指定する。統一せず、個別のレコードごとにタイムゾーンを管理する場合は、 useLegacyDatetimeCode=true でよい。
- 後者のユースケースはあまり思いつかないので、MySQL 5.7系を利用している場合は基本 useLegacyDatetimeCode=false で良い気がする。
- というか、そもそもビジネスロジック〜データ層のコードの時点でUTCに統一しておきたいよね。
useLegacyDatetimeCode=false を指定すると、 java.sql.Timestamp の値をUTCとして捉えて、MySQLのSystem time zoneでの時刻に変換をかける。
- よって、UTCで昼12時だった場合、タイ時間 Asia/Bangkok (UTC+7) での19時としてDB内に保存される。このとき、Java側のタイムゾーンは考慮されない。
- 内部的にタイ時間19時として持ってるのか、UTCの12時で持ってるのかは未確認。
useLegacyDatetimeCode=true (あるいは未指定＝デフォルト値でtrue) の場合、 Java側のタイムゾーンで java.sql.Timestamp を変換した時刻を保存する。
- UTCで昼12時＆ Java側タイムゾーンがAsia/BangkokでMySQL側と一致している場合、挙動は useLegacyDatetimeCode=false の場合と同じ。UTC12時相当が格納される。
- UTCで昼12時＆ Java側タイムゾーンが例えばAustralia/Sydney（現時点でUTC+11, Asia/Bangkokから+4時間）の場合、差分の4時間が加算された値が保存される。

id	system_tz	session_tz	raw_datetime	legacy_code	datetime	timestamp
1	Asia/Bangkok	Asia/Bangkok	2021-02-02T12:00:00+00:00	NULL	2021-02-02 12:00:00	2021-02-12 12:00:00
2	Asia/Bangkok	UTC	2021-02-02T12:00:00+00:00	NULL	2021-02-02 12:00:00	2021-02-12 19:00:00
3	Asia/Bangkok	Australia/Sydney	2021-02-02T12:00:00+00:00	NULL	2021-02-02 12:00:00	2021-02-12 08:00:00
4	Asia/Bangkok	Asia/Bangkok	2021-02-02T12:00:00+00:00	TRUE	2021-02-02 19:00:00	2021-02-02 19:00:00
5	Asia/Bangkok	UTC	2021-02-02T12:00:00+00:00	TRUE	2021-02-02 12:00:00	2021-02-02 12:00:00
6	Asia/Bangkok	Australia/Sydney	2021-02-02T12:00:00+00:00	TRUE	2021-02-02 23:00:00	2021-02-02 23:00:00
7	Asia/Bangkok	Asia/Bangkok	2021-02-02T12:00:00+00:00	FALSE	2021-02-02 19:00:00	2021-02-02 19:00:00
8	Asia/Bangkok	UTC	2021-02-02T12:00:00+00:00	FALSE	2021-02-02 19:00:00	2021-02-02 19:00:00
9	Asia/Bangkok	Australia/Sydney	2021-02-02T12:00:00+00:00	FALSE	2021-02-02 19:00:00	2021-02-02 19:00:00

mysql> SELECT @@system_time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@system_time_zone | @@session.time_zone |
+--------------------+---------------------+
| +07                | SYSTEM              |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> SELECT id, system_tz, session_tz, raw_datetime, (jdbc_param NOT LIKE '%useLegacyDatetimeCode=false') as legacy_jdbc, datetime, timestamp FROM tz_sample;
+----+--------------+------------------+---------------------------+-------------+---------------------+---------------------+
| id | system_tz    | session_tz       | raw_datetime              | legacy_jdbc | datetime            | timestamp           |
+----+--------------+------------------+---------------------------+-------------+---------------------+---------------------+
|  1 | Asia/Bangkok | Asia/Bangkok     | 2021-02-02T12:00:00+00:00 |        NULL | 2021-02-02 12:00:00 | 2021-02-12 12:00:00 |
|  2 | Asia/Bangkok | UTC              | 2021-02-02T12:00:00+00:00 |        NULL | 2021-02-02 12:00:00 | 2021-02-12 19:00:00 |
|  3 | Asia/Bangkok | Australia/Sydney | 2021-02-02T12:00:00+00:00 |        NULL | 2021-02-02 12:00:00 | 2021-02-12 08:00:00 |
|  4 | Asia/Bangkok | Asia/Bangkok     | 2021-02-02T12:00:00+00:00 |           1 | 2021-02-02 19:00:00 | 2021-02-02 19:00:00 |
|  5 | Asia/Bangkok | UTC              | 2021-02-02T12:00:00+00:00 |           1 | 2021-02-02 12:00:00 | 2021-02-02 12:00:00 |
|  6 | Asia/Bangkok | Australia/Sydney | 2021-02-02T12:00:00+00:00 |           1 | 2021-02-02 23:00:00 | 2021-02-02 23:00:00 |
|  7 | Asia/Bangkok | Asia/Bangkok     | 2021-02-02T12:00:00+00:00 |           0 | 2021-02-02 19:00:00 | 2021-02-02 19:00:00 |
|  8 | Asia/Bangkok | UTC              | 2021-02-02T12:00:00+00:00 |           0 | 2021-02-02 19:00:00 | 2021-02-02 19:00:00 |
|  9 | Asia/Bangkok | Australia/Sydney | 2021-02-02T12:00:00+00:00 |           0 | 2021-02-02 19:00:00 | 2021-02-02 19:00:00 |
+----+--------------+------------------+---------------------------+-------------+---------------------+---------------------+
9 rows in set (0.00 sec)

その他まとめ

JDBCの接続作成

            String defaultTimeZone = "Australia/Sydney";
            TimeZone.setDefault(TimeZone.getTimeZone(defaultTimeZone));
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            Connection conn = DriverManager.getConnection(CONNECTION_STRING_BASE + "&useLegacyDatetimeCode=false");

データ書き込み

日付はRFC3339形式のUTCでもらう想定。 SimpleDateFormat で文字列をパースする前に、タイムゾーンを設定しておく。

    private static void insertItem(String datetimeInRFC3339, TimeZone tz, Connection conn) throws ParseException, SQLException {
        SimpleDateFormat rfc3339 = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss+00:00");
        rfc3339.setTimeZone(TimeZone.getTimeZone("UTC"));
        long epoch = rfc3339.parse(datetimeInRFC3339).getTime();  // Get the epoch seconds in UTC
        Timestamp ts = new Timestamp(epoch);

        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO tz_sample" +
                "(system_tz, session_tz, raw_datetime, jdbc_param, datetime, timestamp)" +
                "VALUES (?, ?, ?, ?, ?, ?)");
        pstmt.setString(1, "Asia/Bangkok");
        pstmt.setString(2, tz.getID());
        pstmt.setString(3, datetimeInRFC3339);
        pstmt.setString(4, conn.getMetaData().getURL());
        pstmt.setTimestamp(5, ts);
        pstmt.setTimestamp(6, ts);

        pstmt.execute();
    }

データ読み込み

Epoch秒も一緒に表示しておく。

    private static String getOneRow(ResultSet rs) throws SQLException {
        String systemTZ = rs.getString("system_tz");
        String sessionTZ = rs.getString("session_tz");
        String rawDatetime = rs.getString("raw_datetime");
        String jdbcParam = rs.getString("jdbc_param");
        Date datetime = rs.getDate("datetime");
        Timestamp timestamp = rs.getTimestamp("timestamp");

        int rowNum = rs.getRow();

        Object[] params = {rowNum, systemTZ, sessionTZ, rawDatetime, jdbcParam,
                dateToStr(datetime), timestamp.toString(), timestamp.getTime() / 1000};
        return FMT.format(params);
    }

    private static String dateToStr(Date date) {
        return dateToStr(date, TimeZone.getDefault());
    }

    private static String dateToStr(Date date, String tzString) {
        TimeZone tz = TimeZone.getTimeZone(ZoneId.of(tzString));
        return dateToStr(date, tz);
    }

    private static String dateToStr(Date date, TimeZone tz) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'hh:mm:ss");
        sdf.setTimeZone(tz);
        return sdf.format(date);
    }

ch0geの日記

MySQL + JDBC Timezoneまとめ