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.

CodePudding user response：

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.