Improved OffsetDateTime and ZonedDateTime Mapping in Hibernate 6



Get access to all my video courses, 2 monthly Q&A calls, monthly coding challenges, a community of like-minded developers, and regular expert sessions.

Join the Persistence Hub!


Working with timestamps with timezone information has always been a struggle. Since Java 8 introduced the Date and Time API, the classes OffsetDateTime and ZonedDateTime have become the most obvious and commonly used types to model a timestamp with timezone information. And you might expect that choosing one of them should be the only thing you need to do.

But unfortunately, that isn’t the case if you want to persist this information in a relational database. Even though the SQL standard defines the column type TIMESTAMP_WITH_TIMEZONE, only a few databases support it. Due to that, the JPA specification doesn’t support OffsetDateTime and ZonedDateTime as attribute types. Hibernate 5 supports them by normalizing the timestamp and storing it without timezone information. Hibernate 6 improves on this and provides a clear and flexible mapping for these types.

ATTENTION: In Hibernate 6.0.0.Final, this feature is still marked as @Incubating and might change in future releases.

How to define the timezone handling

In Hibernate 6, you can define the timezone handling in 2 ways:

1. You can specify a default handling by setting the configuration property hibernate.timezone.default_storage property in your persistence.xml. The TimeZoneStorageType enum defines the supported configuration values, which I discuss in more detail in the following section.

<persistence>
    <persistence-unit name="my-persistence-unit">
        <description>Hibernate example configuration - thorben-janssen.com</description>
        <exclude-unlisted-classes>false</exclude-unlisted-classes>

        <properties>
            <property name="hibernate.timezone.default_storage" value="NORMALIZE"/>
			
			...
        </properties>
    </persistence-unit>
</persistence>

2. You can customize the timezone handling of each entity attribute of type ZonedDateTime or OffsetDateTime by annotating it with @TimeZoneStorage and providing a TimeZoneStorageType enum value.

@Entity
public class ChessGame {
    
    @TimeZoneStorage(TimeZoneStorageType.NATIVE)
    private ZonedDateTime zonedDateTime;

    @TimeZoneStorage(TimeZoneStorageType.NATIVE)
    private OffsetDateTime offsetDateTime;
	
	...
}

5 different TimezoneStorageTypes

You can choose between 5 different options to store timezone information. They tell Hibernate to store the timestamp in a column of type TIMESTAMP_WITH_TIMEZONE, persist the timestamp and the timezone in 2 separate columns, or normalize the timestamp to different timezones. I will show you an example of all mappings and how Hibernate handles them in the following sections.

All examples will be based on this simple ChessGame entity class. The attributes ZonedDateTime zonedDateTime and OffsetDateTime offsetDateTime shall store the day and time at which the game was played.

@Entity
public class ChessGame {
    
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private Long id;

    private ZonedDateTime zonedDateTime;

    private OffsetDateTime offsetDateTime;

    private String playerWhite;

    private String playerBlack;

    @Version
    private int version;
	
	...
}

And I will be using this test case to persist a new ChessGame entity object. It sets the zonedDateTime and offsetDateTime attributes to 2022-04-06 15:00 +04:00. After I persisted the entity, I commit the transaction, start a new transaction and fetch the same entity from the database.

EntityManager em = emf.createEntityManager();
em.getTransaction().begin();

ZonedDateTime zonedDateTime = ZonedDateTime.of(2022, 4, 6, 15, 0, 0, 0, ZoneId.of("UTC+4"));     
OffsetDateTime offsetDateTime = OffsetDateTime.of(2022, 4, 6, 15, 0, 0, 0, ZoneOffset.ofHours(4));

ChessGame game = new ChessGame();
game.setPlayerWhite("Thorben Janssen");
game.setPlayerBlack("A better player");
game.setZonedDateTime(zonedDateTime);
game.setOffsetDateTime(offsetDateTime);
em.persist(game);

em.getTransaction().commit();
em.close();


em = emf.createEntityManager();
em.getTransaction().begin();

ChessGame game2 = em.find(ChessGame.class, game.getId());
assertThat(game2.getZonedDateTime()).isEqualTo(zonedDateTime);
assertThat(game2.getOffsetDateTime()).isEqualTo(offsetDateTime);

em.getTransaction().commit();
em.close();

Let’s take a closer look at all 5 TimeZoneStorageType options.

TimeZoneStorageType.NATIVE


WARNING: When I prepared the examples for this article using an h2 database, Hibernate used the column timestamp(6) instead of timestamp with timezone. Please double-check if Hibernate uses the correct column type.

The following section describes the expected behavior.


When configuring TimeZoneStorageType.NATIVE, Hibernate stores the timestamp in a column of type TIMESTAMP_WITH_TIMEZONE. This column type has to be supported by your database.

@Entity
public class ChessGame {
    
    @TimeZoneStorage(TimeZoneStorageType.NATIVE)
    private ZonedDateTime zonedDateTime;

    @TimeZoneStorage(TimeZoneStorageType.NATIVE)
    private OffsetDateTime offsetDateTime;
	
	...
}

In this case, the handling of all read operations is simple, and there is no difference to the handling of any other basic attribute type. The database stores the timestamp with timezone information. Hibernate just needs to set a ZonedDateTime or OffsetDateTime object as a bind parameter or extract it from the result set.

13:10:55,725 DEBUG [org.hibernate.SQL] - insert into ChessGame (offsetDateTime, playerBlack, playerWhite, version, zonedDateTime, id) values (?, ?, ?, ?, ?, ?)
13:10:55,727 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [1] as [TIMESTAMP] - [2022-04-06T15:00+04:00]
13:10:55,735 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [2] as [VARCHAR] - [A better player]
13:10:55,735 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [3] as [VARCHAR] - [Thorben Janssen]
13:10:55,736 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [4] as [INTEGER] - [0]
13:10:55,736 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [5] as [TIMESTAMP] - [2022-04-06T15:00+04:00[UTC+04:00]]
13:10:55,736 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [6] as [BIGINT] - [1]
...
13:10:55,770 DEBUG [org.hibernate.SQL] - select c1_0.id,c1_0.offsetDateTime,c1_0.playerBlack,c1_0.playerWhite,c1_0.version,c1_0.zonedDateTime from ChessGame c1_0 where c1_0.id=?
...
13:10:55,785 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [1] - [2022-04-06T13:00+02:00]
13:10:55,786 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [2] - [A better player]
13:10:55,786 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [3] - [Thorben Janssen]
13:10:55,786 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [4] - [0]
13:10:55,786 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [5] - [2022-04-06T13:00+02:00[Europe/Berlin]]
13:10:55,787 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [4] - [0]

TimeZoneStorageType.NORMALIZE

The TimeZoneStorageType.NORMALIZE is the same handling as provided by Hibernate 5 and the default option in Hibernate 6.

@Entity
public class ChessGame {
    
    @TimeZoneStorage(TimeZoneStorageType.NORMALIZE)
    private ZonedDateTime zonedDateTime;

    @TimeZoneStorage(TimeZoneStorageType.NORMALIZE)
    private OffsetDateTime offsetDateTime;
	
	...
}

It tells Hibernate to let the JDBC driver normalize the timestamp to its local timezone or the timezone defined in the hibernate.jdbc.time_zone setting. It then stores the timestamp without timezone information in the database.

You can’t see this, when you log the bind parameter values of your INSERT statement. Hibernate here still uses the attribute values of your entity object.

11:44:00,815 DEBUG [org.hibernate.SQL] - insert into ChessGame (offsetDateTime, playerBlack, playerWhite, version, zonedDateTime, id) values (?, ?, ?, ?, ?, ?)
11:44:00,819 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [1] as [TIMESTAMP] - [2022-04-06T15:00+04:00]
11:44:00,838 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [2] as [VARCHAR] - [A better player]
11:44:00,839 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [3] as [VARCHAR] - [Thorben Janssen]
11:44:00,839 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [4] as [INTEGER] - [0]
11:44:00,839 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [5] as [TIMESTAMP] - [2022-04-06T15:00+04:00[UTC+04:00]]
11:44:00,840 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [6] as [BIGINT] - [1]

But trace logging of the ResourceRegistryStandardImpl class provides more information about the executed prepared statement. And there, you can see that Hibernate normalized the timestamp from 2022-04-06 15:00+04:00 to my local timezone (UTC+2) and removed the timezone offset 2022-04-06 13:00:00.

11:44:46,247 TRACE [org.hibernate.resource.jdbc.internal.ResourceRegistryStandardImpl] - Closing prepared statement [prep3: insert into ChessGame (offsetDateTime, playerBlack, playerWhite, version, zonedDateTime, id) values (?, ?, ?, ?, ?, ?) {1: TIMESTAMP '2022-04-06 13:00:00', 2: 'A better player', 3: 'Thorben Janssen', 4: 0, 5: TIMESTAMP '2022-04-06 13:00:00', 6: 1}]

When Hibernate reads the timestamp from the database, the JDBC driver gets the timestamp without timezone information and adds its timezone or the timezone defined by the hibernate.jdbc.time_zone setting.

11:55:17,225 DEBUG [org.hibernate.SQL] - select c1_0.id,c1_0.offsetDateTime,c1_0.playerBlack,c1_0.playerWhite,c1_0.version,c1_0.zonedDateTime from ChessGame c1_0 where c1_0.id=?
11:55:17,244 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [1] - [2022-04-06T13:00+02:00]
11:55:17,245 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [2] - [A better player]
11:55:17,245 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [3] - [Thorben Janssen]
11:55:17,245 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [4] - [0]
11:55:17,245 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [5] - [2022-04-06T13:00+02:00[Europe/Berlin]]
11:55:17,247 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [4] - [0]

As you can see in the log output, Hibernate selected the ChessGame entity object from the database and retrieved the correct timestamp. But due to the performed normalization, it is no longer in timezone UTC+4, which I used when I persisted the entity. To avoid any timezone conversions, you need to use TimeZoneStorageType.NATIVE or TimeZoneStorageType.COLUMN.

Timestamp normalization can be risky

Normalizing your timestamps and storing them without timezone information might seem like a simple and obvious solution if your database doesn’t support the column type TIMESTAMP_WITH_TIMEZONE. But it introduces 2 risks:

  1. Changing your local timezone or running servers in different timezones affects the denormalization and results in wrong data.
  2. Timezones with daylight saving time can’t be safely normalized because they have 1 hour that exists in summer- and wintertime. By removing the timezone information, you can no longer distinguish between summer- and wintertime and, therefore, you can’t normalize any timestamp of that period correctly. To avoid this, you should always use a timezone without DST, e.g., UTC.

TimeZoneStorageType.NORMALIZE_UTC


WARNING: As described in HHH-15174, Hibernate 6.0.0.Final doesn’t normalize your timestamp to UTC and instead applies the same normalization as TimeZoneStorageType.NORMALIZE.

The bug got fixed in Hibernate 6.0.1.Final. The following section describes the correct behavior.


The TimeZoneStorageType.NORMALIZE_UTC is very similar to the previously discussed TimeZoneStorageType.NORMALIZE. The only difference is that your timestamp gets normalized to UTC instead of the timezone of your JDBC driver or the timezone configured as hibernate.jdbc.time_zone.

@Entity
public class ChessGame {
    
    @TimeZoneStorage(TimeZoneStorageType.NORMALIZE_UTC)
    private ZonedDateTime zonedDateTime;

    @TimeZoneStorage(TimeZoneStorageType.NORMALIZE_UTC)
    private OffsetDateTime offsetDateTime;
	
	...
}

Hibernate’s handling of the timestamps and the performed normalization during read and write operations is identical to TimeZoneStorageType.NORMALIZE, which I explained in great detail in the previous section.

TimeZoneStorageType.COLUMN

When configuring TimeZoneStorageType.COLUMN, Hibernate stores the timestamp without timezone information and the timezone’s offset to UTC in separate database columns. 

@Entity
public class ChessGame {
    
    @TimeZoneStorage(TimeZoneStorageType.COLUMN)
	@TimeZoneColumn(name = "zonedDateTime_zoneOffset")
    private ZonedDateTime zonedDateTime;

    @TimeZoneStorage(TimeZoneStorageType.COLUMN)
	@TimeZoneColumn(name = "offsetDateTime_zoneOffset")
    private OffsetDateTime offsetDateTime;
	
	...
}

Hibernate uses its naming strategy to map the entity attribute of type ZonedDateTime or OffsetDateTime to a database column. This column stores the timestamp. By default, Hibernate adds the postfix _tz to the name of that column to get the name of the column that contains the timezone offset. You can customize this by annotating your entity attribute with @TimeZoneColumn, as I did in the previous code snippet.

You can clearly see this handling when you persist a new ChessGame entity object and use my recommended logging configuration for development environments.

12:31:45,654 DEBUG [org.hibernate.SQL] - insert into ChessGame (offsetDateTime, offsetDateTime_zoneOffset, playerBlack, playerWhite, version, zonedDateTime, zonedDateTime_zoneOffset, id) values (?, ?, ?, ?, ?, ?, ?, ?)
12:31:45,656 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [1] as [TIMESTAMP_UTC] - [2022-04-06T11:00:00Z]
12:31:45,659 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [2] as [INTEGER] - [+04:00]
12:31:45,660 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [3] as [VARCHAR] - [A better player]
12:31:45,660 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [4] as [VARCHAR] - [Thorben Janssen]
12:31:45,660 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [5] as [INTEGER] - [0]
12:31:45,660 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [6] as [TIMESTAMP_UTC] - [2022-04-06T11:00:00Z]
12:31:45,661 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [7] as [INTEGER] - [+04:00]
12:31:45,661 TRACE [org.hibernate.orm.jdbc.bind] - binding parameter [8] as [BIGINT] - [1]

Based on the timestamp and the timezone offset, Hibernate instantiates a new OffsetDateTime or ZonedDateTime object when it fetches the entity object from the database.

12:41:26,082 DEBUG [org.hibernate.SQL] - select c1_0.id,c1_0.offsetDateTime,c1_0.offsetDateTime_zoneOffset,c1_0.playerBlack,c1_0.playerWhite,c1_0.version,c1_0.zonedDateTime,c1_0.zonedDateTime_zoneOffset from ChessGame c1_0 where c1_0.id=?
...
12:41:26,094 DEBUG [org.hibernate.orm.results.loading.org.hibernate.orm.results.loading.embeddable] - Initializing composite instance [com.thorben.janssen.sample.model.ChessGame.offsetDateTime]
12:41:26,107 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [1] - [2022-04-06T11:00:00Z]
12:41:26,108 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [2] - [+04:00]
12:41:26,109 DEBUG [org.hibernate.orm.results.loading.org.hibernate.orm.results.loading.embeddable] - Created composite instance [com.thorben.janssen.sample.model.ChessGame.offsetDateTime] : 2022-04-06T15:00+04:00
...
12:41:26,109 DEBUG [org.hibernate.orm.results.loading.org.hibernate.orm.results.loading.embeddable] - Initializing composite instance [com.thorben.janssen.sample.model.ChessGame.zonedDateTime]
12:41:26,110 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [6] - [2022-04-06T11:00:00Z]
12:41:26,110 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [7] - [+04:00]
12:41:26,110 DEBUG [org.hibernate.orm.results.loading.org.hibernate.orm.results.loading.embeddable] - Created composite instance [com.thorben.janssen.sample.model.ChessGame.zonedDateTime] : 2022-04-06T15:00+04:00
...
12:41:26,112 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [3] - [A better player]
12:41:26,112 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [4] - [Thorben Janssen]
12:41:26,113 DEBUG [org.hibernate.orm.results] - Extracted JDBC value [5] - [0]

TimeZoneStorageType.AUTO

The handling of TimeZoneStorageType.AUTO depends on Hibernate’s database-specific dialect. If the database supports the column type TIMESTAMP_WITH_TIMEZONE, Hibernate uses TimeZoneStorageType.NATIVE. In all other cases, Hibernate uses TimeZoneStorageType.COLUMN.

@Entity
public class ChessGame {
    
    @TimeZoneStorage(TimeZoneStorageType.AUTO)
    private ZonedDateTime zonedDateTime;

    @TimeZoneStorage(TimeZoneStorageType.AUTO)
    private OffsetDateTime offsetDateTime;
	
	...
}

Conclusion

Even though the SQL standard defines the column type TIMESTAMP_WITH_TIMEZONE, not all databases support it. That makes the handling of timestamps with timezone information surprisingly complex.

As I explained in a previous article, Hibernate 5 supports ZonedDateTime and OffsetDateTime as basic types. It normalizes the timestamp and stores it without timezone information to avoid database compatibility issues.

Hibernate 6 improved this handling by introducing more mapping options. You can now choose between:

Related Articles

Responses

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.