What are JDBC-mysql Driver settings for sane handling of DATETIME and TIMESTAMP in UTC?

Question

Having been burned by mysql timezone and Daylight Savings "hour from hell" issues in the past, I decided my next application would store everything in UTC timezone and only interact with the database using UTC times (not even the closely-related GMT).

I soon ran into some mysterious bugs. After pulling my hair out for a while, I came up with this test code:

try(Connection conn = dao.getDataSource().getConnection();
    Statement stmt = conn.createStatement()) {

    Instant now = Instant.now();

    stmt.execute("set time_zone = '+00:00'");

    stmt.execute("create temporary table some_times("
            + " dt datetime,"
            + " ts timestamp,"
            + " dt_string datetime,"
            + " ts_string timestamp,"
            + " dt_epoch datetime,"
            + " ts_epoch timestamp,"
            + " dt_auto datetime default current_timestamp(),"
            + " ts_auto timestamp default current_timestamp(),"
            + " dtc char(19) generated always as (cast(dt as character)),"
            + " tsc char(19) generated always as (cast(ts as character)),"
            + " dt_autoc char(19) generated always as (cast(dt_auto as character)),"
            + " ts_autoc char(19) generated always as (cast(ts_auto as character))"
            + ")");

    PreparedStatement ps = conn.prepareStatement("insert into some_times "
            + "(dt, ts, dt_string, ts_string, dt_epoch, ts_epoch) values (?,?,?,?,from_unixtime(?),from_unixtime(?))");

    DateTimeFormatter dbFormat = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss").withZone(ZoneId.of("UTC"));

    ps.setTimestamp(1, new Timestamp(now.toEpochMilli()));
    ps.setTimestamp(2, new Timestamp(now.toEpochMilli()));
    ps.setString(3, dbFormat.format(now));
    ps.setString(4, dbFormat.format(now));
    ps.setLong(5, now.getEpochSecond());
    ps.setLong(6,  now.getEpochSecond());
    ps.executeUpdate();             

    ResultSet rs = stmt.executeQuery("select * from some_times");
    ResultSetMetaData md = rs.getMetaData();

    while(rs.next()) {
        for(int c=1; c <= md.getColumnCount(); ++c) {
            Instant inst1 = Instant.ofEpochMilli(rs.getTimestamp(c).getTime());
            Instant inst2 = Instant.from(dbFormat.parse(rs.getString(c).replaceAll("\\.0$", "")));
            System.out.println(inst1.getEpochSecond() - now.getEpochSecond());
            System.out.println(inst2.getEpochSecond() - now.getEpochSecond());
        }
    }
}

Note how the session timezone is set to UTC, and everything in the Java code is very timezone-aware and forced to UTC. The only thing in this entire environment which is not UTC is the JVM's default timezone.

I expected the output to be a bunch of 0s but instead I get this

Each line of output is just subtracting the time stored from the time retrieved. The result in each row should be 0.

It seems the JDBC driver is performing inappropriate timezone conversions. For an application which interacts fully in UTC although it runs on a VM that's not in UTC, is there any way to completely disable the TZ conversions?

i.e. Can this test be made to output all-zero rows?

UPDATE

Using useLegacyDatetimeCode=false (cacheDefaultTimezone=false makes no difference) changes the output but still not a fix:

UPDATE2

Checking the console (after changing the test to create a permanent table), I see all the values are STORED correctly:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 27148
Server version: 5.7.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set time_zone = '-00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM some_times \G
*************************** 1. row ***************************
       dt: 2016-11-18 15:39:51
       ts: 2016-11-18 15:39:51
dt_string: 2016-11-18 15:39:51
ts_string: 2016-11-18 15:39:51
 dt_epoch: 2016-11-18 15:39:51
 ts_epoch: 2016-11-18 15:39:51
  dt_auto: 2016-11-18 15:39:51
  ts_auto: 2016-11-18 15:39:51
      dtc: 2016-11-18 15:39:51
      tsc: 2016-11-18 15:39:51
 dt_autoc: 2016-11-18 15:39:51
 ts_autoc: 2016-11-18 15:39:51
1 row in set (0.00 sec)

mysql>

What's the DB and version of it? Also what JDBC driver are you using and version? (relevant since I already saw some Oracle bugs related to these kind of things) — AlfaTeK, Nov 18 '16 at 06:23
Then say that in the question. It's important information. And tag it `mysql` too. — Andreas, Nov 18 '16 at 06:27
@AlexR Look at this answer http://stackoverflow.com/a/7610174/1166537 — Raghav, Nov 18 '16 at 06:31
See connection property [`cacheDefaultTimezone`](https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html) (since 5.1.35, default: `true`): *Caches client's default time zone. This results in better performance when dealing with time zone conversions in Date and Time data types, however **it won't be aware of time zone changes if they happen at runtime**.* Oops! You're changing time zone as runtime with the `set time_zone = '+00:00'` statement. — Andreas, Nov 18 '16 at 06:34
Try the [`setTimeStamp()`](https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setTimestamp(int,%20java.sql.Timestamp,%20java.util.Calendar)) overload that takes a `Calendar` as an argument and specify the UTC TZ to the `Calendar` you pass in. — Mick Mnemonic, Nov 18 '16 at 09:07
hi @MickMnemonic, the times are already stored in the DB correctly (see UPDATE2), so I'm afraid using `setTimeStamp with UTC TZ Calendar` might produce the expected output but for the wrong reason: storing the wrong value in the DB so that the incorrect conversion on the retrieval fixes it. — Alex R, Nov 18 '16 at 15:47
Sorry, didn't read your post that thoroughly. Maybe try the reciprocal method, [`ResultSet.getTimeStamp()`](https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#getTimestamp(java.lang.String,%20java.util.Calendar)) that takes in a `Calendar`? — Mick Mnemonic, Nov 18 '16 at 16:10
Looking carefully at the output, the wrong results are coming from `getString()` not `getTimeStamp()` — Alex R, Nov 18 '16 at 16:21

Alex R · Accepted Answer · 2016-11-18T16:46:57.903

The solution is to set JDBC connection parameter noDatetimeStringSync=true with useLegacyDatetimeCode=false. As a bonus I also found sessionVariables=time_zone='-00:00' alleviates the need to set time_zone explicitly on every new connection.

There is some "intelligent" timezone conversion code that gets activated deep inside the ResultSet.getString() method when it detects that the column is a TIMESTAMP column.

Alas, this intelligent code has a bug: TimeUtil.fastTimestampCreate(TimeZone tz, int year, int month, int day, int hour, int minute, int seconds, int secondsPart) returns a Timestamp wrongly tagged to the JVM's default timezone, even when the tz parameter is set to something else:

final static Timestamp fastTimestampCreate(TimeZone tz, int year, int month, int day, int hour, int minute, int seconds, int secondsPart) {
    Calendar cal = (tz == null) ? new GregorianCalendar() : new GregorianCalendar(tz);
    cal.clear();

    // why-oh-why is this different than java.util.date, in the year part, but it still keeps the silly '0' for the start month????
    cal.set(year, month - 1, day, hour, minute, seconds);

    long tsAsMillis = cal.getTimeInMillis();

    Timestamp ts = new Timestamp(tsAsMillis);
    ts.setNanos(secondsPart);

    return ts;
}

The return ts would be perfectly valid except when further up in the call chain, it is converted back to a string using the bare toString() method, which renders the ts as a String representing what a clock would display in the JVM-default timezone, instead of a String representation of the time in UTC. In ResultSetImpl.getStringInternal(int columnIndex, boolean checkDateTypes):

                case Types.TIMESTAMP:
                    Timestamp ts = getTimestampFromString(columnIndex, null, stringVal, this.getDefaultTimeZone(), false);

                    if (ts == null) {
                        this.wasNullFlag = true;

                        return null;
                    }

                    this.wasNullFlag = false;

                    return ts.toString();

Setting noDatetimeStringSync=true disables the entire parse/unparse mess and just returns the string value as-is received from the database.

Test output:

The useLegacyDatetimeCode=false is still important because it changes the behaviour of getDefaultTimeZone() to use the database server's TZ.

While chasing this down I also found the documentation for useJDBCCompliantTimezoneShift is incorrect, although it makes no difference: documentation says [This is part of the legacy date-time code, thus the property has an effect only when "useLegacyDatetimeCode=true."], but that's wrong, see ResultSetImpl.getNativeTimestampViaParseConversion(int, Calendar, TimeZone, boolean).

What are JDBC-mysql Driver settings for sane handling of DATETIME and TIMESTAMP in UTC?

1 Answers1

Linked