Conversion of Date to LocalDate does not consider timezone difference

Description

tl;dr

The current implementation of Date to LocalDate conversion in LocalDateJavaDescriptor shifts the date by one day if producer (e.g. MySQL RDBMS) and consumer (e.g. Spring Boot application) are running in different timezones.

Long Issue Description:

Given an application instance and a DB instance in two different timezones:

  • a MariaDB/MySQL which is running on a server in the in the "Europe/Berlin" (UTC+01:00) timezone and

  • a Java (Spring Bootstrap) application on a server in the GMT (UTC+00:00) timezone.

The database contains a table students with a column birthday of a timezone-agnostic type DATE. The appropriate persistence entity looks as following:

The Java application is connected to the producer using the following JDBC URL:

Now, when fetching the entities from the database using a Spring Data Repository all birthday values are shifted by one day, e.g. 1979-12-31 while 1980-01-01 is stored in the database.

Investigation:

For my understanding, the following happens when Hibernate gets the response from the DB:

  1. The DATE value (which is e.g. 1980-01-01) is obtained from producer.

  2. The value is stored in consumer as java.sql.Date, producer timezone is considered (value is 315529200000 "milliseconds", see Online Converter).

  3. Because the consumer is in GMT, the date's internal calendar is represented as 1979-12-31T23:00:00.000Z, which can be still described as correct because the time part is obligatory in the underlying calendar.

  4. ISSUE HERE: The date is now converted to LocalDate in LocalDateJavaDescriptor using Date#toLocalDate which simple cuts off time and timezone. The result is 1979-12-31.

Expectation:

Obviously, if the date 1980-01-01 stored in the DB as a timezone-less value, it should also appear as the same timezone-less value 1980-01-01 after conversion to LocalDate in the persistent entity.

Current Workarouds:

  1. Obviously, switching default JVM timezone using -Duser.timezone=Europe/Berlin option or programmatically by TimeZone.setDefault(TimeZone.getTimeZone("Europe/Berlin")); to the timezone of the producer eleminates this issues.

  2. Another option would be a creation of a custom attribute converter for conversion from java.sql.Date to java.time.LocalDate:

Test Case

The attached

which demonstrates the current behavior of LocalDateJavaDescriptor and of the custom LocalDateAttributeConverter. However, it is hard to reproduce the "real" complex distributed setup depicted above.

Environment

MySQL:5.5.32
mysql-connector-java:5.1.25
spring-boot:1.4.3.RELEASE
hibernate-core:5.2.6

Activity

Show:
Christian Beikov
March 3, 2017, 10:06 AM

Would be great if you could test that. Not sure if the JDBC driver does a conversion of java.sql.Date from System/JVM timezone to the JDBC timezone. If it does, then we would need to add the offset difference to the java.sql.Date milliseconds to correct that.
According to this bug(https://bugs.mysql.com/bug.php?id=71084) it shouldn't do the conversion, thus it shouldn't be requried to change the unwrap method.

Vlad Mihalcea
March 7, 2017, 10:26 AM

Since Hibernate 5.2.3, you can also use the hibernate.jdbc.time_zone configuration property which is described in great detail in this article.

In your case, the property should be set like this:

Now, related to those MySQL settings you configured:

  • zeroDateTimeBehavior=convertToNull

  • useUnicode=true

  • useJDBCCompliantTimezoneShift=true

  • useLegacyDatetimeCode=false

  • serverTimezone=Europe

According to MySQL JDBC Driver docs, if you set the useLegacyDatetimeCode to false will void the effect of useJDBCCompliantTimezoneShift

Setting this property to 'false' voids the effects of "useTimezone," "useJDBCCompliantTimezoneShift," "useGmtMillisForDatetimes," and "useFastDateParsing."

So the useJDBCCompliantTimezoneShift is not taken into consideration:

This is part of the legacy date-time code, thus the property has an effect only when "useLegacyDatetimeCode=true."

Just try with hibernate.jdbc.time_zone and see if it works better.

Also, related to this statement of yours:

The value is stored in consumer as java.sql.Date, producer timezone is considered (value is 315529200000 "milliseconds", see Online Converter).

Because the consumer is in GMT, the date's internal calendar is represented as 1979-12-31T23:00:00.000Z, which can be still described as correct because the time part is obligatory in the underlying calendar.

The java.sql.Date, just like java.util.Date, has no notion of timezone. It's just a point in time, or more cleraly, a number of millis since epoch. What you see when you call toString is just a formatting according to the local TimeZone. However, it has no effect on the way the date/time info is stored.

DimW
March 7, 2017, 11:55 AM

, thanks for clarification. Indeed, some of my configuration properties make the other useless. The issue occurred a while ago but I'm pretty sure I have tried out many different permutations of properties including hibernate.jdbc.time_zone without any success in the depicted scenario. However, I will double check.

As you mentioned, the point in time stored in java.sql.Date we are getting from DB is correct (assuming we use midnight when converting from MySQL's DATE to date and time value) and it is pretty clear that the toString representation of the date uses the local timezone:

MySQL Value (UTC+01:00)

Date Value (UTC+01:00)

Date Value (UTC+00:00)

1980-01-01

1980-01-01T00:00:00.000+01:00

1979-12-31T23:00:00.000Z

The following conversion to LocalDate, however, is obviously uses the same local timezone to determine the date part by cutting off the time part which does not works correctly in all timezones. The fix by attached in the PR to this ticket makes it more clear.

Vlad Mihalcea
March 7, 2017, 2:38 PM

This is not a Hibernate issue. I created a test case to replicate it .

All in all, the same issue can be replicated with plain JDBC while using the same MySQL JDBC configuration properties. The problem here is caused by this parameter:

  • serverTimezone=Europe/Berlin

According to MySQL JDBC Driver documentation, the role of this config is to:

Override detection/mapping of time zone. Used when time zone from server doesn't map to Java time zone

That's what causing the drifting between the local time zone and the server time zone. The actual conversation happens because the MysqlaSession#configureTimezone will dictate if we need to translate from the client TZ to the server TZ.

So, all this happens inside the MySQL Driver, which is out of Hibernate control.

Mixing time zones is always trouble. The vast majority of applications simply set all DB servers to UTC and set all Date/Times according to UTC. You can achieve this goal with the hibernate.jdbc.time_zone configuration property.

So, you have two ways to fix this issue:

  1. Either, you remove the serverTimezone property from the MySQL URL

  2. Or, you keep the serverTimezone property, but then you need to set the JVM timezone to the same TZ.

Christian Beikov
March 16, 2017, 11:33 AM

Ok so I tried to reproduce your issues to but couldn't.

What I tried so far was

  • Start MySQL with TZ +01:00

  • Write LocalDate values into the DB with TZ +00:00 as well as with TZ +02:00

  • Read the values with TZ +00:00, +01:00 and +02:00

When reading, the LocalDates were all correct.

I tried various JDBC configurations. The one you posted as well as configurations without some of the properties i.e. serverTimezone, useJDBCCompliantTimezoneShift etc.

The testcase you wrote wrongly assumes that a java.sql.Date object created with one TZ is going to give the same LocalDate when using toLocalDate() within a different timezone. I don't understand how this can actually happen. Either your assumption about what happens in your case is wrong or you didn't give enough information about your scenario.

If you still think this is a Hibernate problem you gonna have to tell me more details.

Assignee

Vlad Mihalcea

Reporter

DimW

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

backportDecision

None

Components

Affects versions

Priority

Critical
Configure