What is better to persist in db 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?

Answer 1

Short answer

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

---

Long answer

The PostgreSQL JDBC 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 that 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.

The JDBC 4.2 specification doesn't seem to support 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.