Add example about date ISO format

On Fri, Feb 17, 2017 at 04:01:54PM +0000, juha.mustonen@iki.fi wrote:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/9.6/static/functions-formatting.html
Description:

The documentation should include an example how to format datetime entry
into most commonly known ISO format. This is a bit tricky as literal
character needs to included with quotes:

to_char(NOW(), 'YYYY-MM-DD"T"HH24:MI:SSZ')

I know this is a six-year-old idea, but it is still a good one. I have
developed the attached patch I would like to apply to master.

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

Only you can decide what is important to you.

On Tue, 2023-11-21 at 23:33 -0500, Bruce Momjian wrote:

On Fri, Feb 17, 2017 at 04:01:54PM +0000, juha.mustonen@iki.fi wrote:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/9.6/static/functions-formatting.html
Description:

The documentation should include an example how to format datetime entry
into most commonly known ISO format. This is a bit tricky as literal
character needs to included with quotes:

to_char(NOW(), &#39;YYYY-MM-DD&quot;T&quot;HH24:MI:SSZ&#39;)

I know this is a six-year-old idea, but it is still a good one. I have
developed the attached patch I would like to apply to master.

+1 on the idea, but from the context it looks like you added that example
at the regular expression matching functions.

I think the example had best be at "8.5.2. Date/Time Output", in
doc/src/sgml/datatype.sgml around line 2552.

Yours,
Laurenz Albe

On 2023-11-22 10:14 +0100, Laurenz Albe wrote:

On Tue, 2023-11-21 at 23:33 -0500, Bruce Momjian wrote:

On Fri, Feb 17, 2017 at 04:01:54PM +0000, juha.mustonen@iki.fi wrote:

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/9.6/static/functions-formatting.html
Description:

The documentation should include an example how to format datetime entry
into most commonly known ISO format. This is a bit tricky as literal
character needs to included with quotes:

to_char(NOW(), &#39;YYYY-MM-DD&quot;T&quot;HH24:MI:SSZ&#39;)

I know this is a six-year-old idea, but it is still a good one. I have
developed the attached patch I would like to apply to master.

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 93f068edcf..297cafb341 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8489,6 +8489,14 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
</para>
</listitem>

+     <listitem>
+      <para>
+        <literal>to_char(current_timestamp AT TIME ZONE 'UTC',
+        'YYYY-MM-DD"T"HH24:MI:SSZ')</literal> outputs the current UTC

This might be excessive, but should we have an example with other time
zones? ISO 8601 is not limited to UTC. For example:
YYYY-MM-DD"T"HH24:MI:SSOF or YYYY-MM-DD"T"HH24:MI:SSTZH:TZM

Fractional seconds are also possible: YYYY-MM-DD"T"HH24:MI:SS,FF6

+        date/time in <acronym>ISO</acronym> 8601 date/time format.
+      </para>
+     </listitem>
+
</itemizedlist>
</para>

+1 on the idea, but from the context it looks like you added that example
at the regular expression matching functions.

I think the example had best be at "8.5.2. Date/Time Output", in
doc/src/sgml/datatype.sgml around line 2552.

+1 for moving it to section 8.5.2.

--
Erik

On Wed, Nov 22, 2023 at 02:02:02PM +0100, Erik Wienhold wrote:

+     <listitem>
+      <para>
+        <literal>to_char(current_timestamp AT TIME ZONE 'UTC',
+        'YYYY-MM-DD"T"HH24:MI:SSZ')</literal> outputs the current UTC

This might be excessive, but should we have an example with other time
zones? ISO 8601 is not limited to UTC. For example:
YYYY-MM-DD"T"HH24:MI:SSOF or YYYY-MM-DD"T"HH24:MI:SSTZH:TZM

Fractional seconds are also possible: YYYY-MM-DD"T"HH24:MI:SS,FF6

Uh, I think the goal was to show how to output ISO 8601 output with "T".
I assume they can figure out how to customize that.

+        date/time in <acronym>ISO</acronym> 8601 date/time format.
+      </para>
+     </listitem>
+
</itemizedlist>
</para>

+1 on the idea, but from the context it looks like you added that example
at the regular expression matching functions.

I think the example had best be at "8.5.2. Date/Time Output", in
doc/src/sgml/datatype.sgml around line 2552.

+1 for moving it to section 8.5.2.

Okay, I moved it into the "Note" section that talked about ISO 8601
output with "T", in the attached patch.

I will apply this only to master since it is not a correction.

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

Only you can decide what is important to you.

On 2023-11-22 17:58 +0100, Bruce Momjian wrote:

On Wed, Nov 22, 2023 at 02:02:02PM +0100, Erik Wienhold wrote:

+     <listitem>
+      <para>
+        <literal>to_char(current_timestamp AT TIME ZONE 'UTC',
+        'YYYY-MM-DD"T"HH24:MI:SSZ')</literal> outputs the current UTC

This might be excessive, but should we have an example with other time
zones? ISO 8601 is not limited to UTC. For example:
YYYY-MM-DD"T"HH24:MI:SSOF or YYYY-MM-DD"T"HH24:MI:SSTZH:TZM

Fractional seconds are also possible: YYYY-MM-DD"T"HH24:MI:SS,FF6

Uh, I think the goal was to show how to output ISO 8601 output with "T".
I assume they can figure out how to customize that.

Fair point.

+        date/time in <acronym>ISO</acronym> 8601 date/time format.
+      </para>
+     </listitem>
+
</itemizedlist>
</para>

+1 on the idea, but from the context it looks like you added that example
at the regular expression matching functions.

I think the example had best be at "8.5.2. Date/Time Output", in
doc/src/sgml/datatype.sgml around line 2552.

+1 for moving it to section 8.5.2.

Okay, I moved it into the "Note" section that talked about ISO 8601
output with "T", in the attached patch.

I will apply this only to master since it is not a correction.

LGTM.

--
Erik

On 2023-Nov-22, Laurenz Albe wrote:

I think the example had best be at "8.5.2. Date/Time Output", in
doc/src/sgml/datatype.sgml around line 2552.

Actually, isn't that a strange location? Chapter 8.5.2 is about the
datatype itself, and there's already a cross-link to Section 9.8 for
to_char() stuff. Since this is to_char() that the example wants to add,
I think the to_char reference is a more appropriate place -- probably
table "9.31 to_char Examples".

(While scrolling the 9.6 version of this page[1]https://www.postgresql.org/docs/9.6/functions-formatting.html I noticed that, in dark
mode, the <caution> box becomes unreadable because of white text on
yellowish background. Not sure what's an appropriate fix for that, if
any; current versions don't have that problem. Maybe it's better to
leave it alone.)

[1]: https://www.postgresql.org/docs/9.6/functions-formatting.html

--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Porque francamente, si para saber manejarse a uno mismo hubiera que
rendir examen... ¿Quién es el machito que tendría carnet?" (Mafalda)

On Wed, Nov 22, 2023 at 06:26:45PM +0100, Álvaro Herrera wrote:

On 2023-Nov-22, Laurenz Albe wrote:

I think the example had best be at "8.5.2. Date/Time Output", in
doc/src/sgml/datatype.sgml around line 2552.

Actually, isn't that a strange location? Chapter 8.5.2 is about the
datatype itself, and there's already a cross-link to Section 9.8 for
to_char() stuff. Since this is to_char() that the example wants to add,
I think the to_char reference is a more appropriate place -- probably
table "9.31 to_char Examples".

I originally thought it belonged in section 9.8 too, but I think the
value of this example is ISO 8601 and I don't see how we can cleanly
mention that in table 9.31.

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

Only you can decide what is important to you.

On Wed, Nov 22, 2023 at 12:26 PM Bruce Momjian <bruce@momjian.us> wrote:

On Wed, Nov 22, 2023 at 06:26:45PM +0100, Álvaro Herrera wrote:

On 2023-Nov-22, Laurenz Albe wrote:

I think the example had best be at "8.5.2. Date/Time Output", in
doc/src/sgml/datatype.sgml around line 2552.

Actually, isn't that a strange location? Chapter 8.5.2 is about the
datatype itself, and there's already a cross-link to Section 9.8 for
to_char() stuff. Since this is to_char() that the example wants to add,
I think the to_char reference is a more appropriate place -- probably
table "9.31 to_char Examples".

I originally thought it belonged in section 9.8 too, but I think the
value of this example is ISO 8601 and I don't see how we can cleanly
mention that in table 9.31.

Most of our tables have description columns, we could add one here. Or
I've seen us use footnote superscripts before in a table then add the
footnote text after the end of the table.

I'm against incorporating this material into the data types in Chapter 8.

David J.

On Wed, 2023-11-22 at 11:58 -0500, Bruce Momjian wrote:

Okay, I moved it into the "Note" section that talked about ISO 8601
output with "T", in the attached patch.

Fine by me, except that I would rather have "returns" or "produces"
instead of the questionable verb "outputs".

Yours,
Laurenz Albe

On Thu, Nov 23, 2023 at 09:29:39AM +0100, Laurenz Albe wrote:

On Wed, 2023-11-22 at 11:58 -0500, Bruce Momjian wrote:

Okay, I moved it into the "Note" section that talked about ISO 8601
output with "T", in the attached patch.

Fine by me, except that I would rather have "returns" or "produces"
instead of the questionable verb "outputs".

The majority of people seem to want it in table 9.31, so I have moved it
there. It does almost double the width of the displayed table though.
You can see the new output here:

https://momjian.us/tmp/pgsql/functions-formatting.html#FUNCTIONS-FORMATTING-EXAMPLES-TABLE

Patch attached.

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

Only you can decide what is important to you.

On Fri, 2023-11-24 at 12:29 -0500, Bruce Momjian wrote:

On Thu, Nov 23, 2023 at 09:29:39AM +0100, Laurenz Albe wrote:

On Wed, 2023-11-22 at 11:58 -0500, Bruce Momjian wrote:

Okay, I moved it into the "Note" section that talked about ISO 8601
output with "T", in the attached patch.

Fine by me, except that I would rather have "returns" or "produces"
instead of the questionable verb "outputs".

The majority of people seem to want it in table 9.31, so I have moved it
there. It does almost double the width of the displayed table though.
You can see the new output here:

https://momjian.us/tmp/pgsql/functions-formatting.html#FUNCTIONS-FORMATTING-EXAMPLES-TABLE

Patch attached.

Looks good to me.

Yours,
Laurenz Albe

On 2023-11-24 18:29 +0100, Bruce Momjian wrote:

On Thu, Nov 23, 2023 at 09:29:39AM +0100, Laurenz Albe wrote:

On Wed, 2023-11-22 at 11:58 -0500, Bruce Momjian wrote:

Okay, I moved it into the "Note" section that talked about ISO 8601
output with "T", in the attached patch.

Fine by me, except that I would rather have "returns" or "produces"
instead of the questionable verb "outputs".

The majority of people seem to want it in table 9.31, so I have moved it
there.

Fine by me.

It does almost double the width of the displayed table though.
You can see the new output here:

https://momjian.us/tmp/pgsql/functions-formatting.html#FUNCTIONS-FORMATTING-EXAMPLES-TABLE

Patch attached.

Two issues that I fixed in the attached patch:

* the time zone was missing from the result output
* it's called "extended format" not "T format" (the "T" is mandatory
anyway)

--
Erik

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)

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 ;)

--
Erik

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.

On Thu, Dec 7, 2023 at 02:18:28PM -0500, Bruce Momjian wrote:

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.

Patch applied to master. Thanks for all the suggestions.

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

Only you can decide what is important to you.