Hibernate saves/retrieves date minus day if applic

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

For a more detailed explanation, check out this article. You can find a test case in my High-Performance Java Persistence GitHub repository which works just fine.

Answer 2

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

It's used when you are working TimeZoned Date, but from your logs it seems you are not passing TimeZone:

binding parameter [1] as [DATE] - [2018-09-06]

Try to remote property:

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

Answer 3

Ideally, your both servers should be in same time zone and preferred one be in UTC time zone. And to show correct time to user in his timezone; you parse it in browser itself. And while retrieving data from DB; you use UTC time. This way you will not have issue while fetching data from DB

Answer 4

In MySQL...

TIMESTAMP internally stores UTC, but converts to/from the server's timezone based on two settings. Check those settings via SHOW VARIABLES LIKE '%zone%'; Properly configured, the reader may see a different time than the writer (based on tz settings).

DATE and DATETIME take whatever you give it. There is no tz conversion between the string in the client and what is stored in the table. Think of it a storing a picture of a clock. The reader will see the same time string that the writer wrote.

Answer 5

If you add the following parsing to your HQL query, it will return a date without any time zone format or time of day. This is a quick workaround to your issue.

select DATE_FORMAT(date,'%Y-%m-%d') from Entity

Answer 6

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