How to persist OffsetTime and OffsetDateTime with JPA and Hibernate?

Question

How can I persist Java 8 OffsetTime and OffsetDateTime with Hibernate as proper SQL types (TIME_WITH_TIMEZONE and TIMESTAMP_WITH_TIMEZONE)?

I found a solution for LocalTime and LocalDateTime using EnhancedUserTypes in a blog.

How would the user types be for offset data?

Answer 1

Hibernate ORM 5.3 implements the JPA 2.2 standard.

---

Supported types from the Java 8 Date and Time API

The JPA 2.2 specification says that the following Java 8 types are supported:

• java.time.LocalDate
• java.time.LocalTime
• java.time.LocalDateTime
• java.time.OffsetTime
• java.time.OffsetDateTime

Hibernate ORM supports all these types, and even more:

• java.time.ZonedDateTime
• java.time.Duration

---

Entity mapping

Assuming you have the following entity:

@Entity(name = "DateTimeEntity")
public static class DateTimeEntity {

    @Id
    private Integer id;
    
    @Column(name = "duration_value")
    private Duration duration = Duration.of( 20, ChronoUnit.DAYS );
    
    @Column(name = "instant_value")
    private Instant instant = Instant.now();
    
    @Column(name = "local_date")
    private LocalDate localDate = LocalDate.now();
    
    @Column(name = "local_date_time")
    private LocalDateTime localDateTime = LocalDateTime.now();
    
    @Column(name = "local_time")
    private LocalTime localTime = LocalTime.now();
    
    @Column(name = "offset_date_time")
    private OffsetDateTime offsetDateTime = OffsetDateTime.now();
    
    @Column(name = "offset_time")
    private OffsetTime offsetTime = OffsetTime.now();
    
    @Column(name = "zoned_date_time")
    private ZonedDateTime zonedDateTime = ZonedDateTime.now();
    
    //Getters and setters omitted for brevity
}

The DateTimeEntity will have an associated database table that looks as follows:

CREATE TABLE DateTimeEntity (
    id                  INTEGER NOT NULL,
    duration_value      BIGINT,
    instant_value       TIMESTAMP,
    local_date          DATE,
    local_date_time     TIMESTAMP,
    local_time          TIME,
    offset_date_time    TIMESTAMP,
    offset_time         TIME,
    zoned_date_time     TIMESTAMP,
    PRIMARY KEY (id)
)

---

Source: Mapping Java 8 Date/Time entity attributes with Hibernate

Answer 2

Since version 2.2, JPA offers support for mapping Java 8 Date/Time API, like LocalDateTime, LocalTime, LocalDateTimeTime, OffsetDateTime or OffsetTime.

Also, even with JPA 2.1, Hibernate 5.2 supports all Java 8 Date/Time API by default.

In Hibernate 5.1 and 5.0, you have to add the hibernate-java8 Maven dependency.

So, let's assume we have the following Notification entity:

@Entity(name = "Notification")
@Table(name = "notification")
public class Notification {

    @Id
    private Long id;

    @Column(name = "created_on")
    private OffsetDateTime createdOn;

    @Column(name = "notify_on")
    private OffsetTime clockAlarm;

    //Getters and setters omitted for brevity
}

Notice that the createdOn attribute is a OffsetDateTime Java object and the clockAlarm is of the OffsetTime type.

When persisting the Notification:

ZoneOffset zoneOffset = ZoneOffset.systemDefault().getRules()
    .getOffset(LocalDateTime.now());

Notification notification = new Notification()
    .setId(1L)
    .setCreatedOn(
        LocalDateTime.of(
            2020, 5, 1,
            12, 30, 0
        ).atOffset(zoneOffset)
    ).setClockAlarm(
        OffsetTime.of(7, 30, 0, 0, zoneOffset)
    );

entityManager.persist(notification);

Hibernate generates the proper SQL INSERT statement:

INSERT INTO notification (
    notify_on, 
    created_on, 
    id
) 
VALUES (
    '07:30:00', 
    '2020-05-01 12:30:00.0', 
    1
)

When fetching the Notification entity, we can see that the OffsetDateTime and OffsetTime are properly fetched from the database:

Notification notification = entityManager.find(
    Notification.class, 1L
);

assertEquals(
    LocalDateTime.of(
        2020, 5, 1,
        12, 30, 0
    ).atOffset(zoneOffset),
    notification.getCreatedOn()
);

assertEquals(
    OffsetTime.of(7, 30, 0, 0, zoneOffset),
    notification.getClockAlarm()
);