Mapping OffsetDateTime ZoneOffset With Hibernate TimeZoneColumn | Baeldung

1. Overview

It’s common to persist a timestamp in Java applications and generally, the UTC timestamp is preferred to avoid time zone complexities. However, it may be required to store the timestamps with a zone offset sometimes.

In this article, we’ll take a look at how to store both the OffsetDateTime and the ZoneOffset while persisting timestamps.

2. Persisting OffsetDateTime

JPA supports OffsetDateTime out of the box. An OffsetDateTime represents the date and time along with an offset to UTC time.

Let’s define an Entity where we save an OffsetDateTime timestamp:

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

    @Column(name = "inserted_at_timestamp")
    private OffsetDateTime insertedAt;

    // standard setters and getters
}

Now, let’s run a test to verify how JPA persists this information:

@Test
void whenPersistingOffsetDateTime_thenIsPersisted() {
    entityManager.getTransaction().begin();
    Person person1 = new Person();
    person1.setInsertedAt(OffsetDateTime.of(2024,10,31,12,0,0,0, ZoneOffset.ofHours(5)));
    entityManager.persist(person1);

    Person savedEntity = entityManager.createQuery("from Person", Person.class)
      .getSingleResult();
    assertNotNull(savedEntity);
    entityManager.getTransaction().commit();
}

Let’s take a look at the logs for the test:

Hibernate: 
    /* insert for
        com.baeldung.hibernate.timezonecolumn.model.Person */insert 
    into
        Person (inserted_at_timestamp, id) 
    values
        (?, ?)
#1731106113065 | took 0ms | statement | connection 0| url jdbc:p6spy:h2:mem:test
/* insert for com.baeldung.hibernate.timezonecolumn.model.Person */insert into Person (inserted_at_timestamp,id) values (?,?)
/* insert for com.baeldung.hibernate.timezonecolumn.model.Person */insert into Person (inserted_at_timestamp,id) values ('2024-10-31T12:00+05:00',1);

We can see here, Hibernate persists the OffsetDateTime along with the offset in a single column.

JPA utilizes the TIMESTAMP(n) WITH TIME ZONE (or equivalent) SQL type. However, all databases may not support this feature.

3. Persisting OffsetDateTime Using TimeZoneColumn

To persist the ZoneOffset separately or when the database doesn’t support the OffsetDateTime, we can use the @TimeZoneColumn annotation to save the ZoneOffset separately in the database table.

Let’s update the entity we defined previously, by adding a new column and using the @TimeZoneColumn annotation:

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

    @Column(name = "inserted_at_timestamp")
    private OffsetDateTime insertedAt;

    @Column(name = "updated_at_timestamp")
    @TimeZoneStorage(TimeZoneStorageType.COLUMN)
    @TimeZoneColumn(name="last_updated_offset")
    private OffsetDateTime lastUpdatedAt;

    // standard getters and setters
}

JPA stores the offset in a separate column as specified.

Now, let’s verify the same by executing a test:

@Test
void whenPersistingOffsetDateTimeWithTimeZoneColumn_thenIsPersisted() {
    entityManager.getTransaction().begin();
    Person person1 = new Person();
    person1.setLastUpdatedAt(OffsetDateTime.of(2024,10,31,12,0,0,0, ZoneOffset.ofHours(5)));
    entityManager.persist(person1);
    Person savedEntity = entityManager.createQuery("from Person", Person.class)
      .getSingleResult();
    assertNotNull(savedEntity);
    entityManager.getTransaction().commit();
}

Here, we’ve persisted an entity with multiple timestamps to highlight the difference of using @TimeZoneColumn.

Let’s check the logs to confirm that the Hibernate stores the offset in a separate column:

Hibernate: 
    /* insert for
        com.baeldung.hibernate.timezonecolumn.model.Person */insert 
    into
        Person (inserted_at_timestamp, updated_at_timestamp, last_updated_offset, id) 
    values
        (?, ?, ?, ?)
#1731108407719 | took 1ms | statement | connection 0| url jdbc:p6spy:h2:mem:test
/* insert for com.baeldung.hibernate.timezonecolumn.model.Person */insert into Person (inserted_at_timestamp,updated_at_timestamp,last_updated_offset,id) values (?,?,?,?)
/* insert for com.baeldung.hibernate.timezonecolumn.model.Person */insert into Person (inserted_at_timestamp,updated_at_timestamp,last_updated_offset,id) values ('2024-10-30T12:00+05:00','2024-10-31T07:00:00Z',18000,1);

As we can see from the Hibernate query, the ZoneOffset is stored separately in another column along with the timestamp. This split enables easier querying and maintenance.

4. Conclusion

In this tutorial, we’ve discussed different ways to persist OffsetDateTime.

We learned how we can write more maintainable code with @TimeZoneColumn. This also ensures compatibility with different databases which may not support it natively.

As usual, the complete source code is available over on GitHub.