ZonedDateTime emulation

A lot of JDBC drivers only support OffsetDateTime and not ZonedDateTime as it is not mentioned in the JDBC 4.2 specification. However ZonedDateTime support can be emulated using a TIMESTAMP WITH TIME ZONE column holding the timestamp and a VARCHAR field holding the zone id.

The threeten-jpa-zoned-hibernate provides the ZonedDateTimeType Hibernate composite type which maps two such columns to a ZonedDateTime field.

Set Up

You need two columns to hold the timestamp in UTC and the zone id. On our JVM the longest zone id fits in VARCHAR(32) but you can also make the column wider

  TIMESTAMP_UTC TIMESTAMP WITH TIME ZONE,
  ZONE_ID VARCHAR(32),

On the Java side you have to set the type an map both columns

  @Type(type = ZonedDateTimeType.NAME)
  @Columns(columns = {
      @Column(name = "TIMESTAMP_UTC"),
      @Column(name = "ZONE_ID")
  })
  private ZonedDateTime zonedDateTime;

It is important that the TIMESTAMP WITH TIME ZONE column is the first one and the VARCHAR is the second one.

Querying

Querying on the composite attribute (the one in your entity) is quite limited. For example ordering is always done by the timestamp value first and the zone id second, equality comparisons (=) always consider the id of the time zone and a lot of comparisons (<, <=, >=, >) are not available.

These limitations do not apply when the "timestamp_utc" and "zoneid" properties are used in JPQL

SELECT t
FROM MyEntity t
ORDER BY t.zonedDateTimeColumn.zoneid

SELECT t
FROM MyEntity t
WHERE t.zonedDateTimeColumn.timestamp_utc < :value

SELECT t
FROM MyEntity t
WHERE t.zonedDateTimeColumn.zoneid = :zoneid

These options are not available in Criteria API.

Examples

Have a look at the schema sample and the mapping example on how to use it.