How to map the Oracle TIMESTAMP WITH TIME ZONE with JPA

Last modified:

Are you struggling with performance issues in your Spring, Jakarta EE, or Java EE application?

What if there were a tool that could automatically detect what caused performance issues in your JPA and Hibernate data access layer?

Wouldn’t it be awesome to have such a tool to watch your application and prevent performance issues during development, long before they affect production systems?

Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, Micronaut, or Play Framework.

So, rather than fixing performance issues in your production system on a Saturday night, you are better off using Hypersistence Optimizer to help you prevent those issues so that you can spend your time on the things that you love!

Introduction

In this article, I’m going to explain how to map the Oracle TIMESTAMP WITH TIME ZONE with JPA and Hibernate.

While the DATE and TIMESTAMP column types are suitable for mapping the LocalDateTime, the TIMESTAMP WITH TIME ZONE column type allows us to map both the ZonedDateTime and OffsetDateTime.

Oracle TIMESTAMP WITH TIME ZONE

Traditionally, Oracle provided the DATE and TIMESTAMP column types to store timestamp values. The difference between the DATE and TIMESTAMP types is that the TIMESTAMP column type allows us to store fractional seconds while the DATE type can only store the date and time information.

However, neither the DATE nor the TIMESTAMP type can store any time zone information, and for this reason, Oracle provides the TIMESTAMP WITH TIME ZONE column, which extends the TIMESTAMP type and adds support for storing the time zone region or offset. For this reason, we can map it to either a Java ZonedDateTime or OffsetDateTime.

Mapping Oracle TIMESTAMP WITH TIME ZONE using ZonedDateTime

If we store the timestamp region in the TIMESTAMP WITH TIME ZONE column type, then on the Java side, we could use a ZonedDateTime to map this Oracle column type, as illustrated by the following JPA entity mapping:

@Entity
@Table(name = "book")
@DynamicInsert @DynamicUpdate
public class Book {
⠀
    @Id
    private Integer id;
⠀
    @NaturalId
    @Column(length = 15)
    private String isbn;
⠀
    @Column(length = 50)
    private String title;
⠀
    @Column(length = 50)
    private String author;
⠀
    @JdbcType(ZonedDateTimeJdbcType.class)
    @Column(
        name = "published_on", 
        columnDefinition = "TIMESTAMP WITH TIME ZONE"
    )
    private ZonedDateTime publishedOn;
⠀
    @JdbcType(ZonedDateTimeJdbcType.class)
    @Column(
        name = "updated_on", 
        columnDefinition = "TIMESTAMP WITH TIME ZONE"
    )
    private ZonedDateTime updatedOn;
}

By default, Hibernate uses the TimestampWithTimeZoneJdbcType to handle the mapping of ZonedDateTime and OffsetDateTime, but since the TimestampWithTimeZoneJdbcType reads the column value as an OffsetDateTime, we need to explicitly tell Hibernate to use the ZonedDateTimeJdbcType in order to be able to retain the time zone region when fetching the entity from the database.

When persisting the following Book entity:

entityManager.persist(
    new Book()
        .setId(1)
        .setIsbn("978-9730228236")
        .setTitle("High-Performance Java Persistence")
        .setAuthor("Vlad Mihalcea")
        .setPublishedOn(
            ZonedDateTime.of(
                2016, 10, 12, 7, 30, 45, 0,
                ZoneId.of("Europe/Bucharest")
            )
        )
);

Hibernate will execute the following SQL INSERT statement:

INSERT INTO book (
    id,
    isbn,
    title,
    author,
    published_on
) VALUES (
    1,
    '978-9730228236', 
    'High-Performance Java Persistence', 
    'Vlad Mihalcea', 
    '2016-10-12T07:30:45+03:00[Europe/Bucharest]'
)

Internally, the Oracle TIMESTAMP WITH TIME ZONE column type stores the provided time zone region name, so when running the following SQL query:

SELECT title, published_on 
FROM book
WHERE id = 1

We can see that the time zone region was stored exactly as we provided it:

| TITLE                             | PUBLISHED_ON                           |
| --------------------------------- | -------------------------------------- |
| High-Performance Java Persistence | 2016-10-12 07:30:45.0 Europe/Bucharest |

And when fetching the Book entity, we get back the same ZonedDateTime we used when we persisted the entity:

Book book = entityManager.find(Book.class, 1);

assertEquals(
    book.getPublishedOn(),
    ZonedDateTime.of(
        2016, 10, 12, 7, 30, 45, 0,
        ZoneId.of("Europe/Bucharest")
    )
);

When setting the updatedOn property of the Book entity to a timestamp value that matches a different time zone region:

book.setUpdatedOn(
    ZonedDateTime.of(
        2024, 7, 18, 10, 45, 0, 0,
        ZoneId.of("Europe/Paris")
    )
);

We can see that Hibernate propagates the region name to the associated TIMESTAMP WITH TIME ZONE column type:

UPDATE book 
SET updated_on = '2024-07-18T10:45+02:00[Europe/Paris]' 
WHERE id = 1

And when selecting the updated_on column value:

SELECT title, updated_on
FROM book
WHERE id = 1

We can see that the Europe/Paris time zone region was stored by the TIMESTAMP WITH TIME ZONE column:

| TITLE                             | UPDATED_ON                         |
| --------------------------------- | ---------------------------------- |
| High-Performance Java Persistence | 2024-07-18 10:45:00.0 Europe/Paris |

Mapping Oracle TIMESTAMP WITH TIME ZONE using OffsetDateTime

The Oracle TIMESTAMP WITH TIME ZONE column type can also be mapped to a Java OffsetDateTime, as illustrated by the following example:

@Column(
    name = "published_on", 
    columnDefinition = "TIMESTAMP WITH TIME ZONE"
)
private OffsetDateTime publishedOn;

@Column(
    name = "updated_on", 
    columnDefinition = "TIMESTAMP WITH TIME ZONE"
)
private OffsetDateTime updatedOn;

When persisting a Book entity that sets the publishedOn property to the following OffsetDateTime:

entityManager.persist(
    new Book()
        .setId(1)
        .setIsbn("978-9730228236")
        .setTitle("High-Performance Java Persistence")
        .setAuthor("Vlad Mihalcea")
        .setPublishedOn(
            OffsetDateTime.of(
                2016, 10, 12, 7, 30, 45, 0,
                ZoneOffset.of("+02:00")
            )
        )
);

Hibernate will execute the following SQL INSERT statement:

INSERT INTO book (
    id,
    isbn,
    title,
    author,
    published_on
) VALUES (
    1,
    '978-9730228236', 
    'High-Performance Java Persistence', 
    'Vlad Mihalcea', 
    '2016-10-12T07:30:45+02:00'
)

The Oracle TIMESTAMP WITH TIME ZONE column type stores the provided time zone offset, so when fetching the published_on column value for the previously inserted record:

SELECT title, published_on 
FROM book
WHERE id = 1

We can see that the time zone offset was stored exactly as we had provided it:

| TITLE                             | PUBLISHED_ON                |
| --------------------------------- | --------------------------- |
| High-Performance Java Persistence | 2016-10-12 07:30:45.0 +2:00 |

When fetching the Book entity, we get back the same OffsetDateTime value we had used when persisting the entity:

Book book = entityManager.find(Book.class, 1);

assertEquals(
    book.getPublishedOn(),
    OffsetDateTime.of(
        2016, 10, 12, 7, 30, 45, 0,
        ZoneOffset.of("+02:00")
    )
);

Awesome, right?

If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.

Conclusion

The Oracle TIMESTAMP WITH TIME ZONE column type can store both the date and time information of a given timestamp value and the time zone region or offset.

Therefore, if you are using Oracle and want to map a Java ZonedDateTime or OffsetDateTime entity attribute, then the TIMESTAMP WITH TIME ZONE column type is a very convenient option you should consider.