Resolution: Won't Fix
Affects Version/s: 5.2.6
Fix Version/s: None
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.
For my understanding, the following happens when Hibernate gets the response from the DB:
- The DATE value (which is e.g. 1980-01-01) is obtained from producer.
- 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.
- 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.
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.
- 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.
- Another option would be a creation of a custom attribute converter for conversion from java.sql.Date to java.time.LocalDate:
The attached test case 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.