Both my application and the database are on America/Sao_Paulo timezone.
The database is Oracle 19c and the application is using:
My problem is saving LocalDate or LocalDateTime to Oracle using JPA entities.
The table columns are all using the DATE type and the problem happens if I map them to LocalDate or LocalDateTime properties. The problem only happens on days where Daylight Time Savings starts.
Let's say I have 2 properties:
@Column(name = "holiday1")
private LocalDate holiday1;
@Column(name = "holiday2")
private LocalDateTime holiday2;
...
entity.setHoliday1(LocalDate.of(2004, 11, 2));
entity.setHoliday2(LocalDateTime.of(2004, 11, 2, 0, 0, 0));
In this example, 2004-11-02 was the start of DTS in Brazil, on this day there was no midnight, so Hibernate is changing the 00:00 time part to 01:00 whenever it does the INSERT command and I end up with 2004-11-02 01:00:00 on the database.
I tried adding the property spring.jpa.properties.hibernate.jdbc.time_zone=UTC and it made no difference.
If I change the properties' type to OffsetDateTime and do:
entity.setHoliday1(LocalDateTime.of(2004, 11, 2, 0, 0, 0).atOffset(ZoneOffset.UTC));
I end up with 2004-11-02 21:00:00 on the database. However if use both spring.jpa.properties.hibernate.jdbc.time_zone=UTC and OffsetDateTime Hibernate finally inserts 2004-11-02 00:00 on the database.
Is there a way to make Hibernate ignore the local timezone settings and treat LocalDate correctly? I can't globally set the application timezone to UTC or change a huge amount of legacy code to OffsetDateTime.
Even though JPA 2.2 doesn't support ZonedDateTime, Hibernate does. I ended changing the properties to ZonedDateTime and added spring.jpa.properties.hibernate.jdbc.time_zone=UTC.
This was the only way I found to force Hibernate to behave in the way I expected.
Internally, Hibernate uses TimeStamp to handle OffsetDateTime and Date to handle LocalDate. No matter what I tried, it always uses the default timezone to make the conversion before sending the data to the database.
I also tried setting spring.datasource.hikari.data-source-properties.oracle.jdbc.timezoneAsRegion but it made no difference.
Working with Hibernate is quite frustrating, this is not the first time I have to handle some buggy behavior because they re-implemented poorly something already offered by the JDBC driver.
I didn't had the chance to try Hibernate 6, maybe I'll update this answer in the future.
Well, it got even worse: LocalDate is still converted using the default timezone, no matter what I tried.
In addition to the previous property, now I also had to add spring.jpa.properties.hibernate.timezone.default_storage=NORMALIZE.
Despite what the documentation says, after I tried to use NORMALIZE_UTC, Oracle started throwing ORA-01878 errors.