Uploaded image for project: 'Hibernate ORM'
  1. HHH-11396

Conversion of Date to LocalDate does not consider timezone difference

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Critical
    • Resolution: Won't Fix
    • Affects Version/s: 5.2.6
    • Fix Version/s: None
    • Component/s: hibernate-core
    • Labels:
      None
    • Environment:
      MySQL:5.5.32
      mysql-connector-java:5.1.25
      spring-boot:1.4.3.RELEASE
      hibernate-core:5.2.6

      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:

      @Entity(name = "students")
      public class StudentDo {
          @Id
          private Integer id;
      
          @Column
          private LocalDate birthday;
      
          // ...
      }
      

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

      jdbc:mysql://localhost/exampledb?zeroDateTimeBehavior=convertToNull&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Europe/Berlin
      

      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:
        @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;
            }
        }
        

      Test Case

      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.

        Attachments

          Activity

            People

            • Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: