public inbox for [email protected]
help / color / mirror / Atom feedFrom: David E. Wheeler <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: Inconsistent Parsing of Offsets with Seconds
Date: Sat, 22 Jun 2024 12:25:29 -0400
Message-ID: <[email protected]> (raw)
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=# select '2024-06-22T12:35:00+02:30:15'::timestamptz;
timestamptz
------------------------
2024-06-22 10:04:45+00
But the offset seconds are dropped (or rounded away?) by to_timestamp()’s `OF` and `TZ` formats[2]:
david=# select to_timestamp('2024-06-03 12:35:00+02:30:15', 'YYYY-MM-DD HH24:MI:SSOF');
to_timestamp
------------------------
2024-06-03 10:05:00+00
david=# select to_timestamp('2024-06-03 12:35:00+02:30:15', 'YYYY-MM-DD HH24:MI:SSTZ');
to_timestamp
------------------------
2024-06-03 02:05:00-08
The corresponding jsonpath methods don’t like offsets with seconds *at all*:
david=# 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=# 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/timestamp.h#L136-L142
[2]: https://www.postgresql.org/docs/16/functions-formatting.html
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Inconsistent Parsing of Offsets with Seconds
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox