Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.500.171.1.1\))
Subject: Re: Inconsistent Parsing of Offsets with Seconds
From: "David E. Wheeler" <david@justatheory.com>
In-Reply-To: <788148.1719076559@sss.pgh.pa.us>
Date: Sat, 22 Jun 2024 14:10:00 -0400
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <4E09AE2B-918D-40BA-B1DE-534BCC9A873F@justatheory.com>
References: <A1454D02-F747-4A1E-8129-F7E940C7E4EB@justatheory.com>
 <788148.1719076559@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/4E09AE2B-918D-40BA-B1DE-534BCC9A873F%40justatheory.com>
Precedence: bulk

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=E2=80=99m getting into the weeds here. :-)

>> The corresponding jsonpath methods don=E2=80=99t like offsets with =
seconds *at all*:
>=20
> 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.
>=20
>> 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?
>=20
> 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=E2=80=99t work at all in JSON timestamp parsing.

[1]: =
https://github.com/postgres/postgres/blob/70a845c/src/backend/utils/adt/js=
onpath_exec.c#L2420-L2442

So if we were to fix the parsing of offsets in jsonpath, we=E2=80=99d =
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=E2=80=99t exist in any practical sense.

Best,

David