JPA Storing OffsetDateTime with ZoneOffset

Question

I have the following entity class:

@Entity
public class Event {
    private OffsetDateTime startDateTime;
    // ...
}

However, persisting and then reading the entity to/from the database with JPA 2.2 results in a loss of information: the ZoneOffset of startDateTime changes to UTC (the ZoneOffset used by the database timestamp). For instance:

Event e = new Event();
e.setStartDateTime(OffsetDateTime.parse("2018-01-02T09:00-05:00"));

e.getStartDateTime().getHour(); // 9
e.getStartDateTime().getOffset(); // ZoneOffset.of("-05:00")
// ...
entityManager.persist(e); // Stored startDateTime as timestamp 2018-01-02T14:00Z

Event other = entityManager.find(Event.class, e.getId());
other.getStartDateTime().getHour(); // 14 - DIFFERENT
other.getStartDateTime().getOffset(); // ZoneOffset.of("+00:00") - DIFFERENT

I need to use OffsetDateTime: I cannot use ZonedDateTime, because zone rules change (and that also suffers from this information loss anyway). I cannot use LocalDateTime, since an Event happens anywhere in the world and I need the original ZoneOffset from when it happened for accuracy reasons. I can not use Instant because a user fills in the starting time of an event (the event is like an appointment).

Requirements:

• Need to be able to perform >, <, >=, <=, ==, != comparisons on timestamps in JPA-QL

• Need to be able to retrieve the same ZoneOffset as of the OffsetDateTime that was persisted

Answer 1

// Edit: I updated the answer to reflect differences between JPA version 2.1 and 2.2.

// Edit 2: Added JPA 2.2 spec link

---

The Problem with JPA 2.1

JPA v2.1 does not know of java 8 types and will try to stringify the provided value. For LocalDateTime, Instant and OffsetDateTime it will use the toString() method and save the corresponding string to the target field.

This said, you must tell JPA how to convert your value to a corresponding database type, like java.sql.Date or java.sql.Timestamp.

Implement and register the AttributeConverter interface to make this work.

See:

• https://stuetzpunkt.wordpress.com/2015/03/15/persisting-localdate-with-jpa-2-1/
• http://www.adam-bien.com/roller/abien/entry/new_java_8_date_and

Beware of the errornous implementation of Adam Bien: LocalDate needs to be Zoned first.

Using JPA 2.2

Just do not create the attribute converters. They are already included.

// Update 2:

You can see this in the specs here: JPA 2.2 spec. Scroll to the very last page to see, that the time types are included.

If you use jpql expressions, be sure to use the Instant object and also use Instant in your PDO classes.

e.g.

// query does not make any sense, probably.
query.setParameter("createdOnBefore", Instant.now());

This works just fine.

Using java.time.Instant instead of other formats

Anyway, even if you have a ZonedDateTime or OffsetDateTime, the result read from the database will always be UTC, because the database stores an instant in time, regardless of the timezone. The timezone is actually just display information (meta data).

Therefore, I recommend to use Instant instead, and coonvert it to Zoned or Offset Time classses only when needed. To restore the time at the given zone or offset, store either the zone or the offset separately in its own database field.

JPQL comparisons will work with this solution, just keep working with instants all the time.

PS: I recently talked to some Spring guys, and they also agreed that you never persist anything else than an Instant. Only an instant is a specific point in time, which then can be converted using metadata.

Using a composite value

According to the spec JPA 2.2 spec, CompositeValues are not mentioned. This means, they did not make it into the specification, and you cannot persist a single field into multiple database columns at this time. Search for "Composite" and see only mentions related to IDs.

Howerever, Hibernate might be capable of doing this, as mentioned in this comments of this answer.

Example implementation

I created this example with this principle in mind: Open for extension, closed for modification. Read more about this principle here: Open/Closed Principle on Wikipedia.

This means, you can keep your current fields in the database (timestamp) and you only need to add an additional column, which should not hurt.

Also, your entity can keep the setters and getters of OffsetDateTime. The internal structure should be no concern of callers. This means, this proposal should not hurt your api at all.

An implementation might look like this:

@Entity
public class UserPdo {

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

  @Column(name = "display_offset")
  private int offset;

  public void setCreatedOn(final Instant newInstant) {
    this.createdOn = newInstant;
    this.offset = 0;
  }

  public void setCreatedOn(final OffsetDateTime dt) {
    this.createdOn = dt.toInstant();
    this.offset = dt.getOffset().getTotalSeconds();
  }

  // derived display value
  public OffsetDateTime getCreatedOnOnOffset() {
    ZoneOffset zoneOffset = ZoneOffset.ofTotalSeconds(this.offset);
    return this.createdOn.atOffset(zoneOffset);
  }
}

Answer 2

Do not store Instant in the database, use OffsetDateTime.

Always store UTC in the database.

OffsetDateTime appends the offset from UTC/Greenwich, which Instant does not!

And there is no need to add two columns if db supports "TIMESTAMP WITH TIME ZONE".

For jpa use

@Column(name = "timestamp", columnDefinition = "TIMESTAMP WITH TIME ZONE")

With OffsetDateTime it is easy to convert to users LocalDateTime afterwards because you know the offset from UTC obtained by OffsetDateTime.