From: Bruce Momjian <bruce@momjian.us>
To: Erik Wienhold <ewie@ewie.name>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>
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: Thu, 7 Dec 2023 14:18:28 -0500
Message-ID: <ZXIahHVMT4RlhPFo@momjian.us> (raw)
In-Reply-To: <vjia6do4pkjzccmyo3m2ednylhdrqnqdzgdjz2jk3oxni7ucma@o6zscc2ozujd>
References: <lpj35vsvlgkyuhovitnkoluu2wshd2j6flzpdvuunqezrvgb24@uhskknynuu6l>
<202311271422.j6abzoo2x44p@alvherre.pgsql>
<vjia6do4pkjzccmyo3m2ednylhdrqnqdzgdjz2jk3oxni7ucma@o6zscc2ozujd>
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.
Attachments:
[text/x-diff] iso.diff (867B, ../ZXIahHVMT4RlhPFo@momjian.us/2-iso.diff)
download | inline diff:diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgmlindex 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, FMDD HH12:MI:SS')</literal></entry>
<entry><literal>'Tuesday, 6 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>' -.10'</literal></entry>
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: bruce@momjian.us, ewie@ewie.name, alvherre@alvh.no-ip.org, laurenz.albe@cybertec.at, juha.mustonen@iki.fi
Subject: Re: [DOCS] Add example about date ISO format
In-Reply-To: <ZXIahHVMT4RlhPFo@momjian.us>
* 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