Re: Inconsistent Parsing of Offsets with Seconds

On Jun 22, 2024, at 13:15, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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 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

So if we were to fix the parsing of offsets in jsonpath, we’d either have to change the parsing code there or augment the to_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