|

TimezoneStorageType – Hibernate’s improved timezone mapping

ByThorben Janssen

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium 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, 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.

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, not all databases support it.

Due to that, the support in your favorite ORM framework differs a lot:

ATTENTION: This feature is still marked as @Incubating. Its default value got changed in Hibernate 6.2, and it might change again in future releases.

GitHub repository and Codespace integration

Members of the Persistence Hub have exclusive access to this article’s example project on GitHub and GitHub Codespace.

If you’re a member, please log in here.

And if you’re not, please follow this link to learn how the Persistence Hub membership helps you become a better software developer.

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.
Its default value depends on your Hibernate version. Hibernate 6.0 and 6.1 use TimeZoneStorageType.NORMALIZE, and starting with version 6.2, Hibernate uses TimeZoneStorageType.DEFAULT by default.

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

6 different TimezoneStorageTypes

In Hibernate 6.0 and 6.1, you can choose between 5 different options to store timezone information.

And Hibernate 6.2 added DEFAULT as a 6 options.

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 2023-07-08 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(2023, 7, 8, 15, 0, 0, 0, ZoneId.of("UTC+4"));     
OffsetDateTime offsetDateTime = OffsetDateTime.of(2023, 7, 8, 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 6 TimeZoneStorageType options.

NATIVE

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

Starting with Hibernate 6.2, the TimeZoneStorageType.NATIVE has become the default option for all database dialects that support the column type TIMESTAMP_WITH_TIMEZONE.

@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] - [2023-07-08T15: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] - [2023-07-08T15: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] - [2023-07-08T13: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]

NORMALIZE

The TimeZoneStorageType.NORMALIZE is the same handling as provided by Hibernate 5 and the default value of the configuration property hibernate.timezone.default_storage in Hibernate 6.0 and 6.1.

@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] - [2023-07-08T15: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] - [2023-07-08T15: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. There, you can see that Hibernate normalized the timestamp from 2023-07-08 15:00+04:00 to my local timezone (UTC+2) and removed the timezone offset 2023-07-08 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 '2023-07-08 13:00:00', 2: 'A better player', 3: 'Thorben Janssen', 4: 0, 5: TIMESTAMP '2023-07-08 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] - [2023-07-08T13: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. However, due to the performed normalization, it is no longer in the 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.

Mapping issues

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

NORMALIZE_UTC

WARNING: As described in HHH-15174, Hibernate 6.0.0.Final didn’t normalize your timestamp to UTC and instead applies the same normalization as TimeZoneStorageType.NORMALIZE. This was 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 always normalized to UTC.

Since Hibernate 6.2, this is the default handling for database dialects that don’t support the column type timestamp with timezone.

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

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] - [2023-07-08T11: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] - [2023-07-08T11: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] - [2023-07-08T11: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] : 2023-07-08T15: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] - [2023-07-08T11: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] : 2023-07-08T15: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]

Mapping issues

It’s not recommended to store a timezone offset of a future timestamp in your database because timezone rules can change. In that case, you would need to find and update all affected timestamps.

You can avoid this problem by using TimeZoneStorageType.NATIVE or TimeZoneStorageType.NORMALIZE_UTC.

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. Please remember the previously explained warning against storing timezone offsets of future timestamps.

Both mappings preserve the instant represented by the ZonedDateTime or OffsetDateTime and the offset or timezone.

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

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

DEFAULT

Since version 6.2, Hibernate also supports the TimeZoneStorageType.DEFAULT and uses it as the default value of the hibernate.timezone.default_storage property.

Its mapping depends on Hibernate’s database-specific dialect:

Due to that, this mapping preserves the instant represented by the OffsetDateTime or ZonedDateTime, but might not preserve the timezone or offset.

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

    @TimeZoneStorage(TimeZoneStorageType.DEFAULT)
    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:

  • TimeZoneStorageType.NATIVE to store your timestamp in a column of type TIMESTAMP_WITH_TIMEZONE,
  • TimeZoneStorageType.NORMALIZE (default in Hibernate 6.0 and 6.1) to normalize the timestamp to the timezone of your JDBC driver and persist it without timezone information,
  • TimeZoneStorageType.NORMALIZE_UTC to normalize the timestamp to UTC and persist it without timezone information,
  • TimeZoneStorageType.COLUMN to store the timestamp without timezone information and the offset of the provided timezone in 2 separate columns and
  • TimeZoneStorageType.AUTO to let Hibernate choose between TimeZoneStorageType.NATIVE and TimeZoneStorageType.COLUMN based on the capabilities of your database.
  • TimeZoneStorageType.DEFAULT (default in Hibernate >= 6.2) to let Hibernate choose between TimeZoneStorageType.NATIVE and TimeZoneStorageType.NORMALIZE_UTC based on the capabilities of your database.

Similar Posts

Standardized schema generation and data loading with JPA
| |

Standardized schema generation and data loading with JPA

ByThorben Janssen

The creation of database schemas was finally standardized with JPA 2.1. Don’t get me wrong, that doesn’t mean that there were no mechanisms to setup the database before. There were plenty of them. Each JPA implementation provided its own way and configuration parameters for it. While this was better than nothing, it was also very…

Java Text Blocks – Using Multiline Strings with Hibernate & JPA
|

Java Text Blocks – Using Multiline Strings with Hibernate & JPA

ByThorben Janssen

JPA and Hibernate require you to write lots of Strings. You use them to create ad-hoc and named queries with JPQL, to define native SQL queries and to specify the fetching behavior in an EntityGraph. Until Java 13, the lack of multiline Strings in Java made all of these tasks either annoying to implement or…

Leave a Reply

Your email address will not be published. Required fields are marked *

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