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

Question

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

Don't parse it. Use ResultSet.getObject(..., OffsetDateTime.class) — user330315
– user330315, Commented Jun 26, 2020 at 12:04
Do what @a_horse_with_no_name suggested, but if you refuse to do it because you insist on a parsing solution, define a suitable pattern and parse the database column value to an OffsetDateTime by OffsetDateTime odt = OffsetDateTime.parse("2020-06-18 16:15:38+05:30", DateTimeFormatter.ofPattern("uuuu-MM-dd HH:mm:ssxxx"));. — deHaar
– deHaar, Commented Jun 26, 2020 at 12:10
That's a range type, not a timestamp with time zone. Probably the easiest way to retrieve that is to use select lower(..), upper(...) to get the two edges of the range as two separate columns. Then use getObject(..., OffsetDateTime.class) on each of them. — user330315
– user330315, Commented Jun 26, 2020 at 12:14
Does this answer your question? Postgres - split TSTZRANGE in two columns — Arvind Kumar Avinash
– Arvind Kumar Avinash, Commented Jun 26, 2020 at 12:20

Basil Bourque · Accepted Answer · 2020-06-26 14:37:41Z

5

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.

edited Jun 26, 2020 at 14:37

Basil Bourque

347k130 gold badges950 silver badges1.3k bronze badges

answered Jun 26, 2020 at 12:48

Thirumal

9,97616 gold badges71 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anonymous Over a year ago

Great, concise and informative answer. TIL. Thanks.

Arvind Kumar Avinash Over a year ago

Spot on! Do not forget to accept it so that future visitors can also use the solution confidently.

Collectives™ on Stack Overflow

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

1 Answer 1

Split the range type value

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Split the range type value

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related