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: <4E09AE2B-918D-40BA-B1DE-534BCC9A873F@justatheory.com>
Date: Mon, 24 Jun 2024 08:08:14 -0400
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <5AF1E7E9-5856-4BE2-B691-3E41A0A903F5@justatheory.com>
References: <A1454D02-F747-4A1E-8129-F7E940C7E4EB@justatheory.com>
 <788148.1719076559@sss.pgh.pa.us>
 <4E09AE2B-918D-40BA-B1DE-534BCC9A873F@justatheory.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/5AF1E7E9-5856-4BE2-B691-3E41A0A903F5%40justatheory.com>
Precedence: bulk

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 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.
>=20
> [1]: =
https://github.com/postgres/postgres/blob/70a845c/src/backend/utils/adt/js=
onpath_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=3D# 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=E2=80=99d 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,