CODEWITHSUNDEEP
javapostgresqljava-time
mvb13
mvb13

Reputation: 1594

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

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?

Upvotes: 8

Views: 8306

Answers (1)

cassiomolin
cassiomolin

Reputation: 131237

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.

Upvotes: 12

Related Questions