Time Zone Support

Supporting time zones needs both database and driver support.

Databases that support TIMESTAMP WITH TIME ZONE:

• DB2 on z/OS
• HSQL
• H2
• Oracle
• PostgreSQL
• SQL Server

Unfortunately the driver situation is not perfect:

• IBM does not ship a JDBC 4.2 driver for DB2.
• PostgreSQL converts to UTC when storing.

Databases that do not support TIMESTAMP WITH TIME ZONE:

• DB2 LUW
• Derby
• Firebird
• MariaDB
• MySQL

This results in the following support matrix.

	EclipseLink	Hibernate
HSQLDB	❌	threeten-jpa-jdbc42-hibernate
H2	❌	threeten-jpa-jdbc42-hibernate
Oracle	threeten-jpa-oracle-eclipselink	threeten-jpa-jdbc42-hibernate
PostgreSQL	❌	threeten-jpa-jdbc42-hibernate
SQL Server	threeten-jpa-mssql-eclipselink	threeten-jpa-jdbc42-hibernate

JDBC 4.2 and Hibernate (H2, HSQLDB, Oracle, PostgreSQL, SQL Server)

threeten-jpa-jdbc42-hibernate contains Hibernate user types which need to be used using @Type:

@Entity
public class SampleEntity {

  @Column
  @Type(type = Jdbc42OffsetDateTimeType.NAME)
  private OffsetDateTime offsetDateTime;

}

EclipseLink (H2 and SQL Sever)

The threeten-jpa-h2-eclipselink and threeten-jpa-mssql-eclipselink modules contain JPA attribute converters which need to be listed in persistence.xml:

@Entity
public class SampleEntity {

  @Column
  private OffsetDateTime offsetDateTime;

}

Limitations

HSQLDB

The following limitations currently apply:

• ZonedDateTime is not supported, check out ZonedDateTime emulation.
• 2.4.1 or later is needed

H2

The following limitations currently apply:

• ZonedDateTime is not supported, check out ZonedDateTime emulation.
• 1.4.195 or later is recommended

SQL Server

The following limitations currently apply:

• ZonedDateTime is not supported, check out ZonedDateTime emulation.
• Only timestamps in the Gregorian calendar work, timestamps in the Julian calendar do not work.
• set sendTimeAsDatetime=false see mssql-jdbc/issues#1182

PostgreSQL

The following limitations currently apply:

• requires driver 9.4.1208 or later.
• ZonedDateTime is not supported, check out ZonedDateTime emulation.
• OffsetDateTime is supported with the TIMESTAMP WITH TIME ZONE type, however all data is stored in UTC. Eg. when you store 2017-01-16T13:30:02.367+01:00 what is actually stored (and returned) is 2017-01-16T12:30:02.367Z. This is a limitation of PostgreSQL. If you want to keep the information what the original time zone, check out ZonedDateTime emulation.

Oracle

Check out Oracle Time Zone Support

ZonedDateTime emulation

For databases that support only OffsetDateTime there is limited support for ZonedDateTime emulation