7

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?

informatik01
  • 16,038
  • 10
  • 74
  • 104
mvb13
  • 1,514
  • 3
  • 18
  • 33
  • 2
    The correct corresponding Java class is `LocalDateTime`. – MC Emperor Oct 17 '19 at 13:58
  • 1
    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*). – Ole V.V. Oct 22 '19 at 16:11
  • Check [this answer](https://stackoverflow.com/a/67752047/10819573) and [this answer](https://stackoverflow.com/a/67505173/10819573) to learn how to use `java.time` API with JDBC. – Arvind Kumar Avinash Aug 24 '23 at 19:12

1 Answers1

11

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.

cassiomolin
  • 124,154
  • 35
  • 280
  • 359
  • I see that Instant is not supported. Is this wrong to use it? – mvb13 Oct 17 '19 at 14:06
  • But the quote says, "_Note that `ZonedDateTime`, **`Instant`** and `OffsetTime` / `TIME [ WITHOUT TIMEZONE ]` are **not** supported_". – Slaw Oct 17 '19 at 14:18
  • 1
    @Slaw [totally embarrassed here] I don't know where my mind is today. I completely misinterpreted that statement. Let me fix that. – cassiomolin Oct 17 '19 at 14:22
  • 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. – mvb13 Oct 17 '19 at 14:33
  • 1
    @mvb13 As per documentation, you are advised to use either `LocalDateTime` or `OffsetDateTime`. – cassiomolin Oct 17 '19 at 14:55
  • @mvb13 *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. – VGR Oct 17 '19 at 16:42
  • 2
    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. – Basil Bourque Apr 05 '22 at 23:58
  • Adding the updated [JDBC documentation link](https://jdbc.postgresql.org/documentation/query/#using-java-8-date-and-time-classes) – Tom C May 25 '23 at 14:51