What is better to persist in database: OffsetDateTime or Instant?

Question

I want to save a timestamp value into PostgreSQL database. Corresponding column is of the type TIMESTAMP WITHOUT TIME ZONE.

As input Java application gets epoch time (long value), that could be converted into Instant or OffsetDateTime with ZoneOffset.UTC.

What is the best approach? Are there any drawbacks with any of this method?

Statutory

The correct corresponding Java class is LocalDateTime. – Demonolatry

While TIMESTAMP WITHOUT TIME ZONE does live up to (down to?) without time zone, it doesn’t live up to timestamp since it doesn’t uniquely define a point in time. If there’s a way you can, use TIMESTAMP WITH TIME ZONE instead (which lives up to timestamp but not really to with time zone). – Rhodie

Check this answer and this answer to learn how to use java.time API with JDBC. – Counterstroke

Answer 1

Short answer

As per the PostgreSQL JDBC driver documentation, Instant is not supported. But you shouldn't have issues with OffsetDateTime in UTC.

---

Long answer

The PostgreSQL JDBC driver documentation mentions that a corresponding type for TIMESTAMP [ WITHOUT TIMEZONE ] is LocalDateTime, but OffsetDateTime in UTC is also supported. On the other hand, Instant is not supported.

See the quote below:

+--------------------------------+----------------+
| PostgreSQL™                    | Java SE 8      |
+--------------------------------+----------------+
| DATE                           | LocalDate      |
| TIME [ WITHOUT TIMEZONE ]      | LocalTime      |
| TIMESTAMP [ WITHOUT TIMEZONE ] | LocalDateTime  |
| TIMESTAMP WITH TIMEZONE        | OffsetDateTime |
+--------------------------------+----------------+

This is closely aligned with tables B-4 and B-5 of the JDBC 4.2 specification.

Note
ZonedDateTime, Instant and OffsetTime / TIME [ WITHOUT TIMEZONE ] are not supported. Also note that all OffsetDateTime will instances will have be in UTC (have offset 0). This is because the backend stores them as UTC.

And the JDBC 4.2 specification mention support for Instant.

---

Also see the following quote from the OffsetDateTime class documentation (highlight is mine):

OffsetDateTime, ZonedDateTime and Instant all store an instant on the time-line to nanosecond precision. Instant is the simplest, simply representing the instant. OffsetDateTime adds to the instant the offset from UTC/Greenwich, which allows the local date-time to be obtained. ZonedDateTime adds full time-zone rules.

It is intended that ZonedDateTime or Instant is used to model data in simpler applications. This class may be used when modeling date-time concepts in more detail, or when communicating to a database or in a network protocol.

Rosariarosario

I see that Instant is not supported. Is this wrong to use it? – Statutory

But the quote says, "Note that ZonedDateTime, Instant and OffsetTime / TIME [ WITHOUT TIMEZONE ] are not supported". – Lymphocyte

@Lymphocyte [totally embarrassed here] I don't know where my mind is today. I completely misinterpreted that statement. Let me fix that. – Rosariarosario

From OffsetDateTime documentation I see the following: "It is intended that {@code ZonedDateTime} or {@code Instant} is used to model data in simpler applications. This class may be used when modeling date-time concepts in more detail, or when communicating to a database or in a network protocol." Very confusing. My problem is that I'm actually using Instant and it works, but I need to know if have any guarantee on it. – Statutory

@Statutory As per documentation, you are advised to use either LocalDateTime or OffsetDateTime. – Rosariarosario

@Statutory Do not use Instant unless the corresponding column is a WITH TIMEZONE column. You do not want the database driver making assumptions about what timezone to use and converting your data in a way that might or might be correct. – Brahmaputra

While support for Instant is not required by JDBC 4.2 & 4.3, a JDBC driver implementation can choose to support that type. Conversion is trivially easy for such a driver to implement using Instant#atOffset( ZoneOffset.UTC ) and OffsetDateTime#toInstant. Using Instant with such a JDBC driver would be convenient but would make your code less portable. – Misgiving

Adding the updated JDBC documentation link – Trichite