Thread: [DOCS] Add example about date ISO format
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')
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.
Attachment
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(), '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. +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(), '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. > > > > 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.
Attachment
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] 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.
Attachment
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
Attachment
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.
Attachment
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.