*tl;dr*
The current implementation of {{Date}} to {{LocalDate}} conversion in [LocalDateJavaDescriptor|https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/type/descriptor/java/LocalDateJavaDescriptor.java#L115] _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:
{code}
@Entity(name = "students")
public class StudentDo {
@Id
private Integer id;
@Column
private LocalDate birthday;
// ...
}
{code}
The Java application is connected to the producer using the following JDBC URL:
{noformat}
jdbc:mysql://localhost/exampledb?zeroDateTimeBehavior=convertToNull&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Europe/Berlin
{noformat}
Now, when fetching the entities from the database using a [Spring Data Repository|https://docs.spring.io/spring-data/data-commons/docs/1.6.1.RELEASE/reference/html/repositories.html] 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:
# 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|http://www.fileformat.info/tip/java/date2millis.htm]).
# 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|https://github.com/hibernate/hibernate-orm/blob/master/hibernate-core/src/main/java/org/hibernate/type/descriptor/java/LocalDateJavaDescriptor.java#L115] using [Date#toLocalDate|https://docs.oracle.com/javase/8/docs/api/java/sql/Date.html#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:*
# 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}}:
{code}
@Converter(autoApply = true)
public class LocalDateAttributeConverter implements AttributeConverter<LocalDate, Date> {
public static final ZoneId ZONE_EUROPE_BERLIN = ZoneId.of("Europe/Berlin");
@Override
public Date convertToDatabaseColumn(LocalDate locDate) {
if (locDate == null) {
return null;
}
// Untested, probably something like this
ZonedDateTime zonedDateTime = locDate.atStartOfDay(ZONE_EUROPE_BERLIN);
LocalDate producerLocalDate = zonedDateTime.toLocalDate();
Date date = Date.valueOf(producerLocalDate);
return date;
}
@Override
public LocalDate convertToEntityAttribute(Date sqlDate) {
if (sqlDate == null) {
return null;
}
// Fixed implementation considering server timezone
Instant instant = Instant.ofEpochMilli(sqlDate.getTime());
LocalDateTime localDateTime = LocalDateTime.ofInstant(instant, ZONE_EUROPE_BERLIN);
LocalDate localDate = localDateTime.toLocalDate();
return localDate;
}
}
{code}
P *Test Case*
The attached [test case|^HHH-11396 . S zip] which demonstrates the current behavior of {{LocalDateJavaDescriptor}} and of the custom {{LocalDateAttributeConverter}} . Currently not sure how However, it is hard to write a proper test demonstrating this behavior reproduce the "real" complex distributed setup depicted above . Hints welcome. |
|
