Feedback-ID: ia2694551:Fastmail
Date: Mon, 27 Nov 2023 15:22:28 +0100
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Erik Wienhold <ewie@ewie.name>
Cc: Bruce Momjian <bruce@momjian.us>,
	Laurenz Albe <laurenz.albe@cybertec.at>, juha.mustonen@iki.fi,
 pgsql-docs@postgresql.org
Subject: Re: [DOCS] Add example about date ISO format
Message-ID: <202311271422.j6abzoo2x44p@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <lpj35vsvlgkyuhovitnkoluu2wshd2j6flzpdvuunqezrvgb24@uhskknynuu6l>
Archived-At: 
 <https://www.postgresql.org/message-id/202311271422.j6abzoo2x44p%40alvherre.pgsql>
Precedence: bulk

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)