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

Solution 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);
  }
}

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

Comments

[Benjamin Marwell]

I consider this a good question, because the docs do not state a lot about persisting those classes using JPA, and JPA included in EE7 did not support persisting these classes out of the box. Besides this, the difference of "point in time" and "meta information" can be confusing. This is why I upvoted this question and pointed the differences out in my answer. Cheers!

[Benjamin Marwell]

@GeoffreyDeSmet I updated the answer again and explained why adding a field should not be a problem if you coded your PDOs right before. Also, I added spec links.

[Geoffrey De Smet]

Thanks Ben - very nice work - but we have a hard requirement that our Event class (this is a human event, not a system event) has a field OffsetDateTime start and OffsetDateTime end. Non-simple getters isn't a viable workaround for us. For example, the jackson annotations are also on a field level.

[Ole V.V.]

Where is your OffsetDateTime ultimately saved to? A database column of what datatype?

[Ole V.V.]

Is that so? What’s new in JPA 2.2 – Java 8 Date and Time Types

[Benjamin Marwell]

JPA 2.2 was not explicitly mentioned. EE7 only supports 2.1 or 2.0 (not sure which).

[Geoffrey De Smet]

We're already using WildFly 11 with that java.time support. Persisting LocalDateTime isn't a problem. The problem is the loss of the offset information.

[Benjamin Marwell]

Then save you main field aa an instant and store the offset separately. You then can still compare using instants.

[Benjamin Marwell]

Then store the field as instant and the offset in a separate field. OffsetDT can be a derived method, and you can compare using instant.

[Ole V.V.]

To me it sounds simple just to persist OffsetDateTime. Am I missing something (other than the fact that it requires a database field of a datatype that can store the offset with the date-time)?

[Christopher Chianelli]

JPQL comparisons will not work, since the comparisons are done on the data in the database, and there is no one-to-one mapping that preserves ZoneOffset and have all comparisons work. The best one can use is the mapping (x) -> [UTC timestamp of x][ZoneOffset of x], which will work for < >, but fail for <= >= != ==. There is a valid one-to-two mapping (store Timestamp and ZoneOffset separately (which is what @Ben said)), but there no way to do it with an AttributeConverter (that I am aware of)

[Benjamin Marwell]

It works using instant and converter, bc they are always UTC. It's what I already do. The database drops the offset information, this is why it won't work in a single field.

[Christopher Chianelli]

@Ben update your answer to include the fact that you need to store ZoneOffset separately, along with an example new model, and I will mark it as a correct answer (although I do wish one day JPA will allow many-to-one conversions).

[Benjamin Marwell]

@ChristopherChianelli done. Please be so kind an review my code and update. You can now compare instants. Use: setParameter("createdOn", Instant.now()); etc.

[Geoffrey De Smet]

What if we need the fields on the class to remain OffsetDateTime etc (and we stick with field annotations)? I 've been looking into an alternative with a custom type.

[Benjamin Marwell]

@Geoffrey the problem is that you can't save the value as timestamp with additional data. The db is timezone agnostic. You can save astring or long, but this will remove the possibility of comparisons. Why wouldn't the derived methods suffice?

[Geoffrey De Smet]

@Ben We don't want to change the model. In the example above, the Event class needs to hold an OffsetDateTime. That's why I am looking in the "custom value type" approach for Hibernate, so it can store that as an SQL timestamp and an integer offsetInSeconds.

[Benjamin Marwell]

@GeoffreyDeSmet I don't know if you can convert a single attribute into two database columns. Anyway, my suggestion doesn't modify your model, it only extends your model. You can keep the existing getters and setters (open-closed-principle). If you cannot add another field, you're stuck as far as I know. JPA cannot cnovert a single attribute into two columns according to the spec I know.

[Geoffrey De Smet]

You're right, JPA can't. It's an API gap. Hibernate can, with HibernateCompositeType - but it's clunky and I hope that JPA 2.3 considers polishing it and including it.

[Benjamin Marwell]

I just reviewed my answer. It might a good Idea to store the Zone instead of the Offset, because for future times, the offset may change (due to changing daylight saving rules).

[fightlight]

If we talk about Postgres, TIMESTAMP WITH TIME ZONE does not store the timezone actually: phili.pe/posts/timestamps-and-time-zones-in-postgresql So if you stick to db, which really stores timezone info, maybe it makes sense... But more generic and clear approach is to store Instant as was mentioned above.

[pixel]

I am doing exactly what you suggest but when I store my Offsetdayetime my midnight time turns into day before at 23:00.