On Jun 22, 2024, at 14:10, David E. Wheeler <da...@justatheory.com> wrote:
> I believe the former issue is caused by the latter: The jsonpath > implementation uses the formatting strings to parse the timestamps[1], and > since there is no formatting to support offsets with seconds, it doesn’t work > at all in JSON timestamp parsing. > > [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 and abbreviations 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 parsing code to also try the various date/time input functions, as well as the custom formats that *are* defined by the standard. Best,
