From: "David E. Wheeler" <david@justatheory.com>
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.500.171.1.1\))
Subject: Inconsistent Parsing of Offsets with Seconds
Message-Id: <A1454D02-F747-4A1E-8129-F7E940C7E4EB@justatheory.com>
Date: Sat, 22 Jun 2024 12:25:29 -0400
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Archived-At: <https://www.postgresql.org/message-id/A1454D02-F747-4A1E-8129-F7E940C7E4EB%40justatheory.com>
Precedence: bulk

Hackers,

The treatment of timestamptz (and timetz) values with offsets that =
include seconds seems a bit inconsistent. One can create such timestamps =
through the input function:

david=3D# select '2024-06-22T12:35:00+02:30:15'::timestamptz;
      timestamptz      =20
------------------------
 2024-06-22 10:04:45+00

But the offset seconds are dropped (or rounded away?) by =
to_timestamp()=E2=80=99s `OF` and `TZ` formats[2]:

david=3D# select to_timestamp('2024-06-03 12:35:00+02:30:15', =
'YYYY-MM-DD HH24:MI:SSOF');
      to_timestamp     =20
------------------------
 2024-06-03 10:05:00+00

david=3D# select to_timestamp('2024-06-03 12:35:00+02:30:15', =
'YYYY-MM-DD HH24:MI:SSTZ');
      to_timestamp     =20
------------------------
 2024-06-03 02:05:00-08

The corresponding jsonpath methods don=E2=80=99t like offsets with =
seconds *at all*:

david=3D# select jsonb_path_query('"2024-06-03 12:35:00+02:30:15"', =
'$.datetime("YYYY-MM-DD HH24:MI:SSOF")');
ERROR:  trailing characters remain in input string after datetime format

david=3D# select jsonb_path_query('"2024-06-03 12:35:00+02:30:15"', =
'$.timestamp_tz()');
ERROR:  timestamp_tz format is not recognized: "2024-06-03 =
12:35:00+02:30:15"

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? Or =
perhaps there should be a `TZS` format to complement `TZH` and `TZM`?

Best,

David

[1] =
https://github.com/postgres/postgres/blob/70a845c/src/include/datatype/tim=
estamp.h#L136-L142
[2]: https://www.postgresql.org/docs/16/functions-formatting.html