Hackers,
The treatment of timestamptz (and timetz) values with offsets that include seconds seems a bit inconsistent. One can
createsuch 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
ableto 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
