JPA UTC timezone query predicate issue

Question

I am having an issue upgrading Spring Boot Starter Parent from 2.0.9.RELEASE to 2.3.3.RELEASE. To be more specific, I've traced the issue down to migrating from 2.0.9.RELEASE to 2.1.0.RELEASE. Prior to 2.1.0.RELEASE all of our criteria builder predicates that were doing an equals comparison like the below example were working. With the upgrade to 2.1.0.RELEASE we are no longer able to do equals comparisons as no matching row is returned.

Predicate foreverDatePredicate = criteriaBuilder.equal(root.get("deactivatedAt"), LocalDateTime.of(9999, Month.DECEMBER, 31, 0, 0, 0))

It does appear that with these two Spring Boot releases they went from Hibernate Core 5.2.18.FINAL to 5.3.7.FINAL and possibly JPA 2.1 to 2.2. I'm not sure if these version changes are related to the below problem.

The issue appears to be happening because of a timezone mismatch between what is being used as the timezone locally (PST) and the timezone that is being stored in the database (UTC). In my sql editor I was able to verify the hibernate query works just fine and returns the appropriate rows when a timezone is not supplied in the where clause. All of our datetime columns are being stored in our MySQL database in UTC time. I tried using the following timezone related properties in application.properties and our Hibernate JPA properties but both appeared to either not work or just be completely ignored:

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

# Used in jpaProperties property under entityManagerFactory
hibernate.jdbc.time_zone=UTC

As some other threads have suggested I can set the JVM timezone to UTC, and after trying it this works; however, I would rather avoid doing this as it seems like a pretty far-reaching fix for something that should be able to be resolved at a more granular level:

@PostConstruct
public void started() {
    TimeZone.setDefault(TimeZone.getTimeZone("UTC"));
}

I eventually converted our Hibernate entity types that are responsible for mapping to the MySQL datetime columns from LocalDateTime to OffsetDateTime as shown below. OffsetDateTime stores the full timestamp with timezone in UTC time by default and this has resolved the issue and our datetime equals predicates are working again but this has caused a major refactoring headache.

Changed from

@MappedSuperclass
public class BitemporalEntity implements Serializable {

    private static final long serialVersionUID = 2966895098986638738L;

    @Column(name = "deactivated_at")
    protected LocalDateTime deactivatedAt;

To

@MappedSuperclass
public class BitemporalEntity implements Serializable {

    private static final long serialVersionUID = 2966895098986638738L;

    @Column(name = "deactivated_at")
    protected OffsetDateTime deactivatedAt;

I'm just wondering if I'm missing something here. Is using OffsetDateTime instead of LocalDateTime a good resolution to the issue or is there another way?

JPA UTC timezone query predicate issue

0 Answers