On 2023-Nov-27, Erik Wienhold wrote:
> Two issues that I fixed in the attached patch:
>
> * it's called "extended format" not "T format" (the "T" is mandatory
> anyway)
+1
> * the time zone was missing from the result output
This is wrong. Actually, there's no timezone in value, because the use
of the AT TIME ZONE operator makes the type be TIMESTAMP WITHOUT TIME
ZONE. You would notice this if you were to change the incorrect literal
Z in your format string with "TZ" (which expands to empty), with "OF"
(which expands to "+00"), or with "TZH:TZM" (which expands to the full
timezone shift):
# select to_char(current_timestamp at time zone 'America/Santiago', 'YYYY-MM-DD"T"HH24:MI:SSTZ');
to_char
─────────────────────
2023-11-27T11:14:55
=# select to_char(current_timestamp at time zone 'America/Santiago', 'YYYY-MM-DD"T"HH24:MI:SSOF');
to_char
────────────────────────
2023-11-27T11:14:55+00
=# select to_char(current_timestamp at time zone 'America/Santiago', 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM');
to_char
───────────────────────────
2023-11-27T11:14:55+00:00
The final Z in your example just prints a literal Z. (America/Santiago
is UTC-3 currently, not 0, which you would see like this:
=# set timezone to 'America/Santiago';
=# select to_char(current_timestamp, 'YYYY-MM-DD"T"HH24:MI:SSOF');
to_char
────────────────────────
2023-11-27T11:21:37-03
)
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"The Gord often wonders why people threaten never to come back after they've
been told never to return" (www.actsofgord.com)