On Jun 22, 2024, at 13:15, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> It's hard to get excited about this.
I freely admit I’m getting into the weeds here. :-)
>> The corresponding jsonpath methods don’t like offsets with seconds *at all*:
>
> Perhaps that should be fixed, but it's pretty low-priority IMO.
> I doubt there is any standard saying that JSON timestamps need
> to be able to include that.
>
>> I see from the source[1] that offsets between plus or minus 15:59:59
>> are allowed; should the `OF` and `TZ formats be able to parse them?
>
> I'd vote no. to_date/to_char already have enough trouble with format
> strings being squishier than one might expect.
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
So if we were to fix the parsing of offsets in jsonpath, we’d either have to change the parsing code there or augment
theto_timestamp() formats and use them.
Totally agree not a priority; happy to just pretend offsets with seconds don’t exist in any practical sense.
Best,
David