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

user_3391490 0
Viewed 1033

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

timestamp-with-timezone jdbc datetime postgresql java
1 Answers

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.

user_33914900
Related