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.

Have you tried to use [ZonedDateTime](https://docs.oracle.com/javase/8/docs/api/java/time/ZonedDateTime.html) instead of LocalDate in `MyRepository`? — Boris, Sep 06 '18 at 15:59
@Boris, spring data jpa doesn't work with it. I see error: Parameter value [2018-10-02T00:00Z] did not match expected type [java.time.LocalDate (n/a)]; nested exception is java.lang.IllegalArgumentException: Parameter value [2018-10-02T00:00Z] did not match expected type [java.time.LocalDate (n/a)] — gstackoverflow, Sep 06 '18 at 17:44
But according to this [answer](https://stackoverflow.com/a/43898668/3301492) it should be possible. Spring Data JPA 1.11 [supports](https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#new-features.1-11-0) Hibernate 5.2 which [supports](https://docs.jboss.org/hibernate/orm/5.2/userguide/html_single/Hibernate_User_Guide.html#basic-datetime-java8) Java 8 Date/Time Values. — Boris, Sep 07 '18 at 11:46
@Boris, I use newer version. My experiment shows that it is wrong. — gstackoverflow, Sep 07 '18 at 13:19
When you try with `ZonedDateTime`, why do we see the exception complaining about `LocalDate`? Have you left the type of date in `MyInstance` as `LocalDate`? — Boris, Sep 07 '18 at 15:58
As I stated in my answer, I think the `JVM` time zone has to be set to UTC and it is not only a `Hibernate` thing. — git-flo, Sep 15 '18 at 16:21
@gstackoverflow can you try by passing local date with UTC zone id like for current LocalDate date = LocalDate.now(ZoneId.of("UTC")); for your date please just use zone id with it then pass to repository method. — kj007, Oct 05 '18 at 07:47

Vlad Mihalcea · Answer 1 · 2021-01-09T15:22:43.067

12

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.

edited Jan 09 '21 at 15:22

answered Sep 06 '18 at 19:54

Vlad Mihalcea

142,745
71
566
911

But looks like I have it in my yml file. Could you check if I set it correctly? – gstackoverflow Sep 06 '18 at 21:14
How can I check that hibernate got that property? – gstackoverflow Sep 06 '18 at 21:19
Add a breakpoint at [this line](https://github.com/hibernate/hibernate-orm/blob/b3c2c2fe47ef5cf35c5ae1bf64e89efddbc716dc/hibernate-core/src/main/java/org/hibernate/boot/internal/SessionFactoryOptionsBuilder.java#L456) in `SessionFactoryOptionsBuilder`. – Vlad Mihalcea Sep 07 '18 at 05:07
I see in dependencies hibernate-core:5.2.17 – gstackoverflow Sep 07 '18 at 07:45
I found line: ** Object jdbcTimeZoneValue = configurationSettings.get( JDBC_TIME_ZONE ); ** and it evaluates as UTC in org.hibernate.boot.internal.SessionFactoryBuilderImpl – gstackoverflow Sep 07 '18 at 07:52
Then, you need to debug it to see why it does not work for you and compare to [this test](https://github.com/vladmihalcea/high-performance-java-persistence/blob/master/core/src/test/java/com/vladmihalcea/book/hpjp/hibernate/time/UTCTimeZoneMySQLTimestampTest.java) in my high-performance-java-persistence repository which works fine. – Vlad Mihalcea Sep 07 '18 at 07:57
You at least use newer version of hibernate. and older version of mysql(you use 6, but I use 8) – gstackoverflow Sep 07 '18 at 08:40
Try to upgrade the Driver in my repository and see if the test breaks. – Vlad Mihalcea Sep 07 '18 at 08:54
Moreover you verifies data and time. In my case I use localDate which doesn't have time – gstackoverflow Sep 07 '18 at 08:55
Then you should map it to a MySQL `DATE` column and the problem is solved. – Vlad Mihalcea Sep 07 '18 at 10:24
In database I have DATE column. – gstackoverflow Sep 08 '18 at 04:09
any ideas? Do you understand that advise you provided was applied in my question and it doesn't work for me? – gstackoverflow Oct 05 '18 at 07:16
it is not working: Especially in this case"LocalDate in Java, you should use a DATE" and using "spring.jpa.properties.hibernate.jdbc.time_zone=CET" neither helps. – dermoritz Nov 09 '20 at 16:00

git-flo · Answer 2 · 2018-09-15T16:02:28.047

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

your approach will work but we are not able to guarantee that all servers will work in UTC — gstackoverflow, Oct 05 '18 at 07:33
@gstackoverflow this shouldn't be an issue here, because the described `init()` method sets the java TimeZone to UTC, no matter what TimeZone the server is in. — git-flo, Oct 05 '18 at 09:00

score 2 · Answer 3 · answered Nov 20 '20 at 14:01

2

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

answered Nov 20 '20 at 14:01

gliwka

66
2

score 0 · Answer 4 · answered Sep 08 '18 at 22:54

0

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

answered Sep 08 '18 at 22:54

Pavan

121
4

2

Actually, ideally the **time zone of your servers should be *irrelevant***. Your programming should specify explicitly the desired/expected time zones as as optional arguments to the date-time classes rather than relying implicitly on current default time zones. – Basil Bourque Sep 08 '18 at 23:01
I agree with the comment of Basil Bourque. In times of (globaly) distributed databases and microservice architectures you clearly have to understand the time zone topic of both, your application and the underlying database. Nevermind, the time zones should be abstracted and therefore irrelevant. – git-flo Sep 15 '18 at 15:53

score 0 · Answer 5 · answered Sep 12 '18 at 12:27

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.

system_time_zone UTC time_zone SYSTEM – gstackoverflow Oct 05 '18 at 07:33 — gstackoverflow, Oct 05 '18 at 07:33

score 0 · Answer 6 · answered Oct 08 '18 at 14:30

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

score 0 · Answer 7 · answered Oct 11 '18 at 18:06

0

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

answered Oct 11 '18 at 18:06

Alain Cruz

4,757
3
25
43

score 0 · Answer 8 · answered May 21 '20 at 16:48

I've done everything as instructed in the answers before, like

Adding spring.jpa.properties.hibernate.jdbc.time_zone=America/Sao_Paulo
Setting default timezone: TimeZone.setDefault(TimeZone.getTimeZone("America/Sao_Paulo"))

but none of them worked until I add the parameter serverTimezone=America/Sao_Paulo in my JDBC url:

jdbc:mysql://localhost:3306/aurorabuzz-test?serverTimezone=America/Sao_Paulo

Now it's working just fine!

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

P.S.

P.S.2

8 Answers8