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.
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.