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,