Wrong LocalDate stored with hibernate

Question

I'm trying to store inside the database the date of a restaurant booking but, even though the date I submit is correct, hibernate stores inside the database a date one day before the one I submitted. I don't know why... it's probably a timezone problem but I can't understand why... the date should not be affected by the timezones.

Here is my spring boot properties file:

spring:
  thymeleaf:
    mode: HTML5
    encoding: UTF-8
    cache: false
  jpa:
    database: MYSQL
    hibernate:
      ddl-auto: update
    properties:
      hibernate:
        locationId:
          new_generator_mappings: false
        dialect: org.hibernate.dialect.MySQL5InnoDBDialect
        jdbc:
          time_zone: UTC
  datasource:
    driver:
      class: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/databaseName?useSSL=false&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC
    username: username
    password: **********

I'm from Italy, so my timezone is this:

GMT/UTC + 1h during Standard Time
GMT/UTC + 2h during Daylight Saving Time

Currently we are UTC + 2h.

The object I'm storing is this one:

@Entity
public class Dinner {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long dinnerId;

    private LocalDate date;
    ...

The controller I'm using to intercept the POST request is this:

@PreAuthorize("hasRole('USER')")
@PostMapping
public String createDinner(@RequestParam(value="dinnerDate") String dinnerDate, Principal principal, Model model){
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        LocalDate date = LocalDate.parse(dinnerDate, formatter);
        dinnerService.createDinner(date);
        return "redirect:/dinners?dinnerDate=" + dinnerDate;
}

Which calls the service method createDinner that call the Jpa method save to store the object. I'm using thymeleaf to handle the html templates. If I submit the date 30/6/2019 inside the database I get 29/6/2019. When I retrieve the Dinner object by date, if I insert 30/6/2019, I get the Dinner with the date 29/6/2019. So it seems that spring handle the date by itself in a weird way... considering some sort of timezone but I don't know how to disable or handle it. Any idea?

what is the column database type of the `date`? Also please add the code for `createDinner(LocalDate date)` method. + look here:https://stackoverflow.com/questions/43476364/hibernate-with-java-8-localdate-localdatetime-in-database : "...So if you want an appointment of 9 AM, you should use a LocalTime or LocalDateTime recorded in a database column of type TIMESTAMP WITHOUT TIME ZONE... " — Dmitrii Bocharov, Jun 23 '19 at 08:31
some more questions: did you check what is actually in database after you save the entity? And how do you retrieve the Dinner object? — Dmitrii Bocharov, Jun 23 '19 at 08:49
As I said: "If I submit the date 30/6/2019 inside the database I get 29/6/2019" so yes I checked... that's the whole point! I retrieve the dinner object by calling the JpaRepository method findById. But the problem is in the database... it saves a wrong day... — Stefano Sambruna, Jun 23 '19 at 08:51
What MySQL version and connector are you using? Are you also using a third-party connection pool? This could be relevant: https://bugs.mysql.com/bug.php?id=71084 — riddle_me_this, Jun 23 '19 at 23:18
You can try this: https://stackoverflow.com/a/60906163/8536903 to set-up local correctly — Lukk17s, Mar 30 '20 at 07:15

chafiq gouasmia · Answer 1 · 2020-10-28T12:30:42.427

0

Assuming your time zone is : Europe/Italy , You have to set up your serverTimezone variable like this :

url: jdbc:mysql://localhost:3306/databaseName?useSSL=false&useUnicode=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=Europe/Italy

edited Oct 28 '20 at 12:30

answered Dec 05 '19 at 12:17

chafiq gouasmia

41
6

score 0 · Answer 2 · answered Jun 26 '20 at 14:15

0

same problem (and same country! :-) ).

I suspect that if hibernate or jpa are set with timezone UTC, while the machine is set with default timezone == Europe/Rome when a date is persisted, then it will be converted automatically from machine timezone to database timezone, which is not a bad feature if you have all dates stored in UTC format on the DB.

The problem happens when you convert the date before persisting: it gets converted twice. At least, this is my case.

Still looking for the best solution! In case I'll find one, then I'll add it later to the answer.

answered Jun 26 '20 at 14:15

funder7

1,622
17
30

Have you find a solution? It seems to be a bug in my opinion: https://bugs.mysql.com/bug.php?id=104822&thanks=4 – Vojtěch Sep 03 '21 at 11:51
Nope.. I did not work on that project anymore. But I was using PostgreSql, the link is on the mysql forum...it may relate, but I'm not that expert with Hibernate to tell! – funder7 Sep 03 '21 at 16:20

score 0 · Accepted Answer · answered Jun 29 '20 at 10:00

You do not need to define a format for the pattern yyyy-MM-dd. LocalDate#parse uses DateTimeFormatter.ISO_LOCAL_DATE by default which means LocalDate.parse("2020-06-29") works without applying a format explicitly.
Since you already know that date-time in your time-zone is different from that in UTC, you should never consider just a date; rather you should consider both date and time e.g. 11:30 PM at UTC on 2020-06-29 will fall on 2020-06-30 in your time-zone. Therefore, the first thing you should do is to change the type of the field as TIMESTAMP in the database.
Once you have changed the type of the field to TIMESTAMP, change the method, createDinner as follows:
```
LocalDateTime dinnerDateTime = LocalDateTime.of(LocalDate.parse(dinnerDate), LocalTime.of(0, 0, 0, 0));
OffsetDateTime odt = dinnerDateTime.atOffset(ZoneOffset.UTC);
dinnerService.createDinner(odt);
```
Then inside DinnerService (or DinnerServiceDAO wherever you have written the logic to insert/update record in the database):
```
pst.setObject(index, odt);
```
where pst represents the object of PreparedStatement and index represents the index (starting with 1) of this field in your insert/update query.

Wrong LocalDate stored with hibernate

3 Answers3