Formatting java.time Instant values from PostgreSQL Timestamp values

Question

I'm new to the java.time formats in Java 8 and later, but I'm reasonably comfortable with Joda-Time and I'm very familiar with Java's java.util.Date, java.util.Calendar, and DateFormat classes along with ISO 8601.

I'm using PostgreSQL 9.3 with jOOQ 3.6.4, with a foo table column containing a timestamp:

bar timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP

I retrieve that bar value using jOOQ, and try to print it out using java.time's DateTimeFormatter.ISO_OFFSET_DATE_TIME:

DateTimeFormatter timestampFormatter = DateTimeFormatter.ISO_OFFSET_DATE_TIME;
Cursor<FooRecord> fooRecordCursor = createDSLContext().selectFrom(FOO).fetchLazy();
for(FooRecord fooRecord : fooRecordCursor) {
  System.out.println(timestampFormatter.format(fooRecord.getBar().toInstant());
}

This throws an UnsupportedTemporalTypeException:

Caused by: java.time.temporal.UnsupportedTemporalTypeException: Unsupported field: Year
  at java.time.Instant.getLong(Instant.java:608)
  at java.time.format.DateTimePrintContext$1.getLong(DateTimePrintContext.java:205)
  at java.time.format.DateTimePrintContext.getValue(DateTimePrintContext.java:298)
  at java.time.format.DateTimeFormatterBuilder$NumberPrinterParser.format(DateTimeFormatterBuilder.java:2543)
  at java.time.format.DateTimeFormatterBuilder$CompositePrinterParser.format(DateTimeFormatterBuilder.java:2182)
  at java.time.format.DateTimeFormatterBuilder$CompositePrinterParser.format(DateTimeFormatterBuilder.java:2182)
  at java.time.format.DateTimeFormatterBuilder$CompositePrinterParser.format(DateTimeFormatterBuilder.java:2182)
  at java.time.format.DateTimeFormatter.formatTo(DateTimeFormatter.java:1745)
  at java.time.format.DateTimeFormatter.format(DateTimeFormatter.java:1719)

But if I use my own custom ISO8601DateFormat which extends the old-school SimpleDateFormat, I can parse the value just fine:

final DateFormat timestampFormatter = new ISO8601DateFormat();
...
  System.out.println(timestampFormatter.format(fooRecord.getBar());

I find this very confusing:

The jOOQ bar field accessor returns a java.sql.Timestamp. In the Java 8 version I convert that to an Instant, but why would that reduce the amount of information available?
An Instant is supposed to be an absolute point of time --- isn't it simply based upon a long offset just like JodaTime and Java Date?
Why is DateTimeFormatter.ISO_OFFSET_DATE_TIME expecting a Year field from an Instant? Shouldn't the formatter just convert the long offset to a date/time in the current time zone, and retrieve the year from that? I wouldn't expect any instant to contain a Year field.

In short: If my SimpleDateFormat-based ISO8601DateFormat works fine for a Timestamp from PostgreSQL, why can't DateTimeFormatter.ISO_OFFSET_DATE_TIME figure out how to format the Instant version of the same value?

toLocalDatetime() wouldn't work either as it misses the time zone field needed for DateTimeFormatter.ISO_OFFSET_DATE_TIME format. — flo
– flo, Commented Oct 28, 2015 at 6:29

flo · Accepted Answer · 2015-10-27 16:54:54Z

Short answer:
It's because the java.time classes are separating the concepts "point in time" and "time as a human sees it" whereas Timestamp/Date don't.

Long answer:
You are right, an Instant is representing a single point in the time line. That's why it is not possible to give a correct/unique answer to the question "what's the year/day/time?". It depends on where on the world the question is asked: In New York it differs from Sidney.
But your DateTimeFormatter is asking exactly this question. And that is why you get an UnsupportedTemporalTypeException.

Date and its subclass Timestamp on the other hand are mixing up the two concepts. While internally storing a long "point in time", they "answer" if asked for their year. Usually they are assuming the local time zone of the system to pin the the long-offset to an specific time zone.
This is error-prone and let to introduction of Calendar, JodaTime and java.time.

Now, why is your DateTimeFormatter not smart enough to align Instant to the default TimeZone?
DateTimeFormatter works on the TemporalAccessor interface and does not differ between concrete implementations like Instant, LocalDateTime or ZonedDateTime. There are legitimate formatting cases for all of those implementations and I assume it is simply not feasible to check the concrete object's compatibility with the given format let alone to perform correct conversions.

The solution: You have to align your timestamp to a timezone/offset yourself:

System.out.println(timestampFormatter.format(
    fooRecord.getBar().toLocalDateTime().atZone(ZoneId.systemDefault()));

Collectives™ on Stack Overflow

Formatting java.time Instant values from PostgreSQL Timestamp values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related