Hibernate not parsing values around DST transitions correctly

Question

I've got a problem where an Instant that occurs during the DST transition is being persisted correctly to the database, but when read back is returning a different value.

Specifically, I am in Europe/London and am having a problem with 2021-10-31T01:04:00Z - the instant I get back is 2021-10-31T00:04:00Z.

I've created a simple application to demonstrate this - it persists three events, one before the DST transition, the only previously mentioned thats during the DST transition, and then one after. I would expect the input and output data to always be the same.

It outputs the following:

ZoneId=Europe/London

Persisting:
2021-10-30T01:04:00Z
2021-10-31T01:04:00Z
2021-11-01T01:04:00Z

Native Query:
clob1: '2021-10-30 02:04:00+01'
clob2: '2021-10-31 01:04:00+01'
clob3: '2021-11-01 01:04:00+00'

Hibernate Object:
2021-10-30T01:04:00Z
2021-10-31T00:04:00Z
2021-11-01T01:04:00Z

:

import java.text.MessageFormat;
import java.time.Instant;
import java.time.ZoneId;
import java.util.Properties;
import java.util.stream.Stream;    
import org.h2.Driver;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.cfg.AvailableSettings;
import org.hibernate.cfg.Configuration;
import org.hibernate.dialect.H2Dialect;

public class StoreData {
  public static void main(final String[] args) {
    System.out.println(MessageFormat.format("ZoneId={0}", ZoneId.systemDefault()));

    final Properties properties = new Properties();
    properties.setProperty(AvailableSettings.DIALECT, H2Dialect.class.getName());
    properties.setProperty(AvailableSettings.URL, "jdbc:h2:mem:test");
    properties.setProperty(AvailableSettings.DRIVER, Driver.class.getName());

    try (Session session = new Configuration().setProperties(properties).addAnnotatedClass(Event.class)
      .buildSessionFactory().openSession()) {
      final Transaction transaction = session.beginTransaction();

      // Table creation done explicitly to show types used
      session.createSQLQuery("CREATE TABLE EVENT(ID BIGINT, DATE TIMESTAMP WITH TIME ZONE)").executeUpdate();

      System.out.println("Persisting:");
      Stream.of("2021-10-30T01:04:00Z", "2021-10-31T01:04:00Z", "2021-11-01T01:04:00Z").map(Instant::parse)
        .forEach(instant -> {
          System.out.println(instant);
          final Event e = new Event();
          e.setDate(instant);
          e.setId(instant.toEpochMilli());
          session.persist(e);
        });

      transaction.commit();
      System.out.println();

      System.out.println("Native Query:");
      session.createNativeQuery("SELECT CAST(date as TEXT) FROM event").getResultList()
        .forEach(System.out::println);

      System.out.println();

      System.out.println("Hibernate Objects:");
      session.getEntityManagerFactory().createEntityManager().createQuery("FROM Event", Event.class)
        .getResultList().stream().map(Event::getDate).forEach(System.out::println);
    }
  }
}

:

import java.time.Instant;    
import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class Event {
    @Id
    private long id;
    private Instant date;

    public long getId() {
    return id;
    }

    public void setId(final long id) {
    this.id = id;
    }

    public Instant getDate() {
    return date;
    }

    public void setDate(final Instant date) {
    this.date = date;
    }
}

pom.xml:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.me</groupId>
    <artifactId>help</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <dependencies>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-entitymanager</artifactId>
            <version>5.6.7.Final</version>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <version>2.1.210</version>
        </dependency>
    </dependencies>
</project>

Interestingly if I move to Hibernate v6, it looks to work correctly?

ZoneId=Europe/London

Persisting:
2021-10-30T01:04:00Z
2021-10-31T01:04:00Z
2021-11-01T01:04:00Z

Native Query:
clob1: '2021-10-30 01:04:00+00'
clob2: '2021-10-31 01:04:00+00'
clob3: '2021-11-01 01:04:00+00'

Hibernate Objects:
2021-10-30T01:04:00Z
2021-10-31T01:04:00Z
2021-11-01T01:04:00Z

To be explicit: Europe/London timezone, H2 in-memory database.

What database product? What version of that database product? What *exactly* is the data type of that database column (show the `CREATE TABLE` code)? — Basil Bourque, Apr 05 '22 at 14:35
Are you aware that many database query tools and DB admin tools unfortunately have an anti-feature of applying a default time zone to the date-time value *after* retrieval from the database server? — Basil Bourque, Apr 05 '22 at 14:38
What is the default time zone of your database session used during your native query? What is the default time zone of your JVM? — Basil Bourque, Apr 05 '22 at 14:40
@BasilBourque I'm using H2 (as specified in the `pom.xml`), and letting Hibernate create the database (`hbm2ddl.auto`). It's an in-memory database created when the example is run. My timezone is `Europe/London` — Jakg, Apr 05 '22 at 15:01
Those details need to go into your Question rather than a Comment. — Basil Bourque, Apr 05 '22 at 15:09
You still need to show the data type of the column. That fact is crucial to understanding your issue. There is a world of difference between `TIMESTAMP WITH TIME ZONE` and `TIMESTAMP WITHOUT TIME ZONE `. — Basil Bourque, Apr 05 '22 at 15:20
@BasilBourque the datatype Hibernate v5 has used is `TIMESTAMP`. v6 has used `TIMESTAMP WITH TIMEZONE`. — Jakg, Apr 05 '22 at 15:40
Wow, that is an unfathomably radical change between versions. That justifies my mistrust of such code generators. Which is in use in your particular case? — Basil Bourque, Apr 05 '22 at 15:46
@BasilBourque I'm using Hibernate v5. I'm not able to test this in my example application, but I'm not sure the only difference in the Hibernate versions - if I change the column definition in my actual application I get the same behaviour either way. — Jakg, Apr 05 '22 at 16:10

Jakg · Accepted Answer · 2022-04-11T14:40:58.123

1

I've got this working by setting the AvailableSettings.JDBC_TIME_ZONE to UTC and changing the column type to TIMEZONE.

I believe this to be an incompatibility between Hibernate v5 & H2's driver.

edited Apr 11 '22 at 14:40

answered Apr 11 '22 at 14:18

Jakg

922
12
39

Hibernate not parsing values around DST transitions correctly

1 Answers1