Hibernate saves/retrieves date minus day if application uses another timezone than MySQL

Question

I have an application started on tomcat on MACHINE_A with timezone GMT+3.

I use remote MySQL server started on MACHINE_B with timezone UTC.

We use spring-data-jpa for persistence.

As an example of the problem, I will show the repository:

public interface MyRepository extends JpaRepository<MyInstance, Long> {
    Optional<MyInstance> findByDate(LocalDate localDate);
}

If I pass localDate for 2018-09-06, I get entities where the date is 2018-09-05(previous day)

In the logs I see:

2018-09-06 18:17:27.783 TRACE 13676 --- [nio-8080-exec-3] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [DATE] - [2018-09-06]

I googled that question a lot and found several articles with the same content(for example https://moelholm.com/2016/11/09/spring-boot-controlling-timezones-with-hibernate/)

So, I have the following application.yml:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/MYDB?useUnicode=true&characterEncoding=utf8&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=UTC
    username: root
    password: *****
  jpa:
    hibernate:
      naming:
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
    properties:
      hibernate:
        show_sql: true
        use_sql_comments: true
        format_sql: true
        type: trace
        jdbc:
          time_zone: UTC

But it doesn't help.

We use the following connector:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.12</version>
</dependency>

How can I resolve my problem?

P.S.

I tried to run both applications with the same time zone. In this case, everything works as expected.

P.S.2

I tried to use MySQL driver 6.0.6 version but it doesn't change anything.

Answer 1

If you're using LocalDate in Java, you should use a DATE column in MySQL. This way the problem will be solved.

If you use LocalDateTime, try setting the property like this in Spring Boot:

spring.jpa.properties.hibernate.jdbc.time_zone=UTC

To see it working in action, you can find a test case in my High-Performance Java Persistence GitHub repository which demonstrates how this setting works with MySQL.

Answer 2

I faced similar issues while creating some integration tests for a spring-boot application using hibernate. The database I used here was postgreSQL.

As another answer correctly points out, you can set the hibernate.jdbc.time_zone=UTC property like discribed. Nevermind this didn't solve my issues, so I had to set the JVM default time zone with the help of the following in my spring-boot applications main class:

@PostConstruct
public void init(){
    TimeZone.setDefault(TimeZone.getTimeZone("UTC"));   // It will set UTC timezone
    System.out.println("Spring boot application running in UTC timezone :"+new Date());   // It will print UTC timezone
}

This should also solve your problems. You can gather more informations here.

Reason

I guess your problem (retrieving date - 1 day) comes from your specific setup. If your application is running in UTC and requesting timestamps from a database in GMT+3 it resolves in a earlier date, because the applications context (JVM and Hibernate are responsible here) is 3 hours behind the database context in UTC. Simple example:

2018-12-02 00:00:00 - 3hours = 2018-12-01 21:00:00

As you are only looking to the dates: 2018-12-02 - 3hours = 2018-12-01

Answer 3

There was a bug in the MySQL Connector prior to version 8.0.22, see Spring data query for localdate returns wrong entries - minus one day