Spring Boot: Time Zone configuration using Hibernate

Motivation

Recently, I was working on a Spring Boot application that needs to query database (Mysql in my case), extract, compute and save a large amount of data (about 100 000 row).

I started first with a small prototype of 5000 lines to check my algorithme. All was good, except one line that triggered this error:

      org.springframework.dao.InvalidDataAccessApiUsageException: HOUR_OF_DAY: 0 -> 1; nested exception is java.lang.IllegalArgumentException: HOUR_OF_DAY: 0 -> 1
      at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:384)
      at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:246)
      at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:491)
     ...

        Caused by: java.lang.IllegalArgumentException: HOUR_OF_DAY: 0 -> 1
      at java.util.GregorianCalendar.computeTime(GregorianCalendar.java:2829)
      at java.util.Calendar.updateTime(Calendar.java:3393)
      at java.util.Calendar.getTimeInMillis(Calendar.java:1782)
      at com.mysql.cj.jdbc.io.JdbcDateValueFactory.createFromDate(JdbcDateValueFactory.java:66)
      at com.mysql.cj.jdbc.io.JdbcDateValueFactory.createFromDate(JdbcDateValueFactory.java:39)
      at com.mysql.cj.core.io.BaseDecoratingValueFactory.createFromDate(BaseDecoratingValueFactory.java:46)
      at com.mysql.cj.core.io.BaseDecoratingValueFactory.createFromDate(BaseDecoratingValueFactory.java:46)
      at ...

and a tons of questions triggered in my mind! How even this error triggered ?! This specific line contains the date 2009-06-01. SO What happened in this specific date for god sake, and hope I found the answer: First time Morocco started to implement summer time was ... Yes right: 2009-06-01. and from here comes my motivation to write this post, how you can control the timezone used by Hibernate when it communicates timestamps with the database in a spring boot app.

Error cause:

It turns out that the Calendar objects Hibernate send to the JDBC API gets completely ignored. When the driver runs in the useLegacyDatetimeCode=true mode, which is default. The driver get’s the argument – and then simply choose to make another Calendar like this: Calendar.getInstance(). The effect of that, is that the driver uses the timezone that is default to the JVM.

1- Configuring the JVM’s default timezone

2 possible ways here:

launch a Spring Boot runnable JAR:

java -Duser.timezone=<TimeZone> -jar springboot-timezones.jar

from inside your Spring Boot application

    @SpringBootApplication
    public class Application {
     
      @PostConstruct
      void started() {
        TimeZone.setDefault(TimeZone.getTimeZone("TimeZone"));
      }
     
      public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
      }
     
    }

You could also put the TimeZone code into the main method. But putting the TimeZone code into a @PostConstruct callback like this, ensures that it is also effective when you run tests or wrap up the application as a Web ARchive (WAR). Be careful about competing with other initializer methods that use JPA during startup.

In fact, as of writing this post, setting the default timezone is the only truly portable solution for vanilla JPA solutions. JPA doesn’t allow you to control this.

2- Mysql Configuration

As explained above, It turns out that MySQL by default runs in a useLegacyDatetimeCode=true mode. You need to deactivate it for Hibernate to be able to do it’s magic. Luckily that’s easy, just configure it in the JDBC connection URL:

jdbc:mysql://<ip>/<db>?serverTimezone=TimeZone&useLegacyDatetimeCode=false

While this solution worked for me and solved my issue, but it clearly tightly bound to the specific driver.

Hibernate Tip: Configure Hibernate to control the timezone

This is simply done by adding this config to your application.yml file

 spring:
   jpa:
     properties:
       hibernate:
         jdbc:
           time_zone = TimeZone

With this, Hibernate automatically translates Dates from to your TimeZone when communicating with the database. much cleaner. But you still have, in order to this to work in mysql, to disable the useLegacyDatetimeCode.

Please feel free to comment if you have any suggestion or recommendation.