How to convert PostgreSQL timestamp with time zone to Java Instant or OffSetDateTime?

Question

How to convert PostgreSQL timestamp with time zone to Java Instant or OffSetDateTime?

PostgreSQL timestamp with time zone format: 2020-06-18 16:15:38+05:30

Getting the following exception in Java 11.7 - Ubuntu for Instant.parse("2020-06-18 16:15:38+05:30".replace(" ", "T"))

Exception in thread "main" java.time.format.DateTimeParseException: Text '2020-06-18T16:15:38+05:30' could not be parsed at index 19
at java.base/java.time.format.DateTimeFormatter.parseResolved0(DateTimeFormatter.java:2046)
at java.base/java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1948)
at java.base/java.time.Instant.parse(Instant.java:395)
at OffSetDateTimeExample.main(OffSetDateTimeExample.java:9)

but it works in Java 13.

Any help to make it work in Java 11

Answer 1

Split the range type value

tstzrange is a range type in Postgres.

Split the PostgreSQL tstzrange in query by calling the lower  and upper functions.

select 
    *, 
    lower(tstzrange) as lower_tstzrange, 
    upper(tstzrange) as upper_tstzrange 
from announcement
;

and use it in Resultset as OffsetDateTime

TstzRange.builder()
    .startDateTime(rs.getObject("lower_tstzrange", OffsetDateTime.class))
    .endDateTime(rs.getObject("upper_tstzrange", OffsetDateTime.class))
            .build()

---

Thanks to a_horse_with_no_name and Arvind Kumar Avinash for saving my day & learnt splitting range datatypes.