Re: Inconsistent Parsing of Offsets with Seconds - Mailing list pgsql-hackers

From David E. Wheeler
Subject Re: Inconsistent Parsing of Offsets with Seconds
Date
Msg-id 5AF1E7E9-5856-4BE2-B691-3E41A0A903F5@justatheory.com
Whole thread Raw
In response to Re: Inconsistent Parsing of Offsets with Seconds  ("David E. Wheeler" <david@justatheory.com>)
List pgsql-hackers
On Jun 22, 2024, at 14:10, David E. Wheeler <david@justatheory.com> wrote:

> I believe the former issue is caused by the latter: The jsonpath implementation uses the formatting strings to parse
thetimestamps[1], and since there is no formatting to support offsets with seconds, it doesn’t work at all in JSON
timestampparsing. 
>
> [1]: https://github.com/postgres/postgres/blob/70a845c/src/backend/utils/adt/jsonpath_exec.c#L2420-L2442

A side-effect of this implementation of date/time parsing using the to_char templates is that only time zone offsets
andabbreviations are supported. I find the behavior a little surprising TBH: 

david=# select to_timestamp('2024-06-03 12:35:00America/New_York', 'YYYY-MM-DD HH24:MI:SSTZ');
ERROR:  invalid value "America/New_York" for "TZ"
DETAIL:  Time zone abbreviation is not recognized.

Unless the SQL standard only supports offsets and abbreviations, I wonder if we’d be better off updating the above
parsingcode to also try the various date/time input functions, as well as the custom formats that *are* defined by the
standard.

Best,





pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: speed up a logical replica setup
Next
From: Christoph Berg
Date:
Subject: Re: Changing default -march landscape