Date: Thu, 7 Dec 2023 14:18:28 -0500
From: Bruce Momjian <bruce@momjian.us>
To: Erik Wienhold <ewie@ewie.name>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>,
	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: <ZXIahHVMT4RlhPFo@momjian.us>
References: <lpj35vsvlgkyuhovitnkoluu2wshd2j6flzpdvuunqezrvgb24@uhskknynuu6l>
 <202311271422.j6abzoo2x44p@alvherre.pgsql>
 <vjia6do4pkjzccmyo3m2ednylhdrqnqdzgdjz2jk3oxni7ucma@o6zscc2ozujd>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="phvOWs4fWz15ap9d"
Content-Disposition: inline
In-Reply-To: <vjia6do4pkjzccmyo3m2ednylhdrqnqdzgdjz2jk3oxni7ucma@o6zscc2ozujd>
Archived-At: 
 <https://www.postgresql.org/message-id/ZXIahHVMT4RlhPFo%40momjian.us>
Precedence: bulk


--phvOWs4fWz15ap9d
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Mon, Nov 27, 2023 at 04:52:20PM +0100, Erik Wienhold wrote:
> On 2023-11-27 15:22 +0100, Alvaro Herrera wrote:
> > 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):
> 
> Thanks for the explanation.  Bruce used literal Z in the format string.
> I just corrected the sample output to match the format.
> 
> Or we just use current_timestamp along with pattern TZH:TZM which also
> adds less to the width of the displayed table in case Bruce is concerned
> about that ;)

Yes, there were a few problems with my initial patch.  First, I should
have used "Z" instead of a Z which could be interpreted as a format
specification;  I had already done that for "T" in the example.

Second, I was missing the trailing "Z" in the output --- not sure how
that happened.

Erik and Alvaro made a great point --- we are using a literal "Z" as the
time zone output specification, but as Alvaro pointed out, there is no
way to get the time zone _name_ or even the offset from the AT TIME ZONE
value, so we must just pass the literal "Z" from the input to the output.
This proves Alvaro's point on this issue:

	SELECT pg_typeof(current_timestamp AT TIME ZONE 'America/Santiago');
	          pg_typeof
	-----------------------------
	 timestamp without time zone

Updated patch attached.  Thank you for the feedback.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.

--phvOWs4fWz15ap9d
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="iso.diff"

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 20da3ed033..cec21e42c0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8748,6 +8748,12 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
         <entry><literal>to_char(current_timestamp, 'FMDay,&nbsp;FMDD&nbsp;&nbsp;HH12:MI:SS')</literal></entry>
         <entry><literal>'Tuesday,&nbsp;6&nbsp;&nbsp;05:39:18'</literal></entry>
        </row>
+       <row>
+        <entry><literal>to_char(current_timestamp AT TIME ZONE
+        'UTC', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')</literal></entry>
+        <entry><literal>'2022-12-06T05:39:18Z'</literal>,
+        <acronym>ISO</acronym> 8601 extended format</entry>
+       </row>
        <row>
         <entry><literal>to_char(-0.1, '99.99')</literal></entry>
         <entry><literal>'&nbsp;&nbsp;-.10'</literal></entry>

--phvOWs4fWz15ap9d--