From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Erik Wienhold <ewie@ewie.name>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: juha.mustonen@iki.fi
Cc: pgsql-docs@postgresql.org
Subject: Re: [DOCS] Add example about date ISO format
Date: Mon, 27 Nov 2023 15:22:28 +0100
Message-ID: <202311271422.j6abzoo2x44p@alvherre.pgsql> (raw)
In-Reply-To: <lpj35vsvlgkyuhovitnkoluu2wshd2j6flzpdvuunqezrvgb24@uhskknynuu6l>
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)
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: pgsql-docs@postgresql.org
Cc: alvherre@alvh.no-ip.org, ewie@ewie.name, bruce@momjian.us, laurenz.albe@cybertec.at, juha.mustonen@iki.fi
Subject: Re: [DOCS] Add example about date ISO format
In-Reply-To: <202311271422.j6abzoo2x44p@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox