CODEWITHSUNDEEP
javamysqldatetimejdbc
peach
peach

Reputation: 747

Why does the timezone specified in the JDBC connection string effect how Instants are stored in MySQL?

In our project (Spring Boot 2.2.3, MySQL 5.7, Hibernate, Java 14) we are having all date-related fields as datatype java.time.Instant. In our MySQL all fields are of type DATETIME.

When I specify a connectionString like jdbc:mysql://localhost/mydb?characterEncoding=UTF-8&useLegacyDatetimeCode=false&serverTimezone=Europe/Paris for my JDBC connection and I have a value of 2020-07-13T00:00:00Z in my Entity, in the database 2020-07-13T02:00:00Z gets persisted (viewed via IntelliJ/DataGrip). When I read it again with the JDBC connection I receive it correctly with 2020-07-13T00:00:00Z.

The display of the time in the table view in IntelliJ doesn't seem to effected by the serverTimeZone I set, so I hope it displays the plain value as stored in the DB.

When I change the connectionString to jdbc:mysql://localhost/mydb?characterEncoding=UTF-8&useLegacyDatetimeCode=false&serverTimezone=UTC for my JDBC connection and I have a value of 2020-07-13T00:00:00Z in my Entity, in the database 2020-07-13T00:00:00Z gets persisted (viewed via IntelliJ/DataGrip). When I read it again with the JDBC connection I receive it correctly with 2020-07-13T00:00:00Z.

So it looks like I have an Instant, Java/MySQL assumes it's UTC and converts it to the timezone specified in the connectionstring and therefore adding the one hour during winter time/two hours during summer time for my timezone.

What I would like to understand is who performs these timezone adaptions and why. Because I understood from the MySQL documentation, that these adaptions should not happen for type DATETIME.

MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME.) By default, the current time zone for each connection is the server's time. The time zone can be set on a per-connection basis. As long as the time zone setting remains constant, you get back the same value you store. If you store a TIMESTAMP value, and then change the time zone and retrieve the value, the retrieved value is different from the value you stored.

Upvotes: 1

Views: 2248

Answers (1)

rzwitserloot
rzwitserloot

Reputation: 102902

MySQL 'DATETIME' stores sign, year, day, hour, minute, second, and fractional second. It is the equivalent of LocalDateTime.

Yet, you are writing epoch millis (in new time API terms: java.time.Instant) to it. Traditionally, datetime values in databases were presumed to be instant-based (as in, epoch-millis, not human YMDhms based), hence why java.sql.Timestamp extends java.util.Date (note that j.u.Date is a bald-faced lie. It represents epoch-millis, not date at all). The proof of that is also found in the API: All methods except those that fetch/set epoch-millis are deprecated in j.u.Date.

Note that the modern JDBC spec in fact requires support for the new java.time types such as LocalDate, and I can confirm that e.g. the JDBC drivers for postgres get this right.

In the past, the JDBC APIs would grow new methods - there'd be a .setLocalTime(idxOfQuestionMark, localTimeInstance) method in PreparedStatement, and a .getLocalTime(idxOrNameOfColumn) in ResultSet, for example. But, no longer. Any newly added types are to be used thusly:

LocalTime lt = resultSet.getObject(idxOrNameOfColumn, LocalTime.class);
preparedStatement.setObject(paramIndex, lt);

The first thing to try to do is to get the way mysql stores data, and the way java represents the data it got from mysql / sends to mysql, to line up. Because if the MySQL db has a YMDhms value and the only way your java code can observe this value is via an object whose inner storage allows it to represent only epoch-millis, well, guess what? Somebody somewhere is doing a timezone-based conversion because you can't go from epochmillis to YMDhms or vice versa without it. If you then convert right back you're just introducing opportunities for error.

However, it's mysql, and mysql is not a very good database, so odds are good that the above doesn't work (even though the JDBC spec more or less demands support for LocalTime, LocalDate, LocalDateTime, and ZonedDateTime, and LDT is an excellent match for what MySQL's DATETIME columns actually contain). So, if that doesn't work....

you're going to have to dance around it, and accept that conversion occurs. That will mean that what you're observing (connection timezone has an effect on what you read) will remain. One solution to that is to forget about DATETIME (after all, if indeed LDT instances can't be sent to/received from the JDBC MySQL driver), you have no way to reliably set or get such columns at all. Redesign your DB definitions to use TIMESTAMP WITH TIMEZONE instead (this is like ZonedDateTime, which is close enough to Instant (epoch-millis) that conversion shouldn't be an issue any more, though it's still suboptimal). Lock down the zone on both sides, and you can reliably convert from epoch-millis to that zone and back.

Or, better yet, find a better DB engine :)

Upvotes: 2

Related Questions