Re: [DOCS] Add example about date ISO format
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 https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: [DOCS] Add example about date ISO format
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 https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
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}');
to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS')
'Tuesday, 6 05:39:18'
+
+to_char(current_timestamp AT TIME ZONE
+'UTC', '-MM-DD"T"HH24:MI:SS"Z"')
+'2022-12-06T05:39:18Z',
+ISO 8601 extended format
+
to_char(-0.1, '99.99')
' -.10'
Re: [DOCS] Add example about date ISO format
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
Re: [DOCS] Add example about date ISO format
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', '-MM-DD"T"HH24:MI:SSTZ'); to_char ─ 2023-11-27T11:14:55 =# select to_char(current_timestamp at time zone 'America/Santiago', '-MM-DD"T"HH24:MI:SSOF'); to_char 2023-11-27T11:14:55+00 =# select to_char(current_timestamp at time zone 'America/Santiago', '-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, '-MM-DD"T"HH24:MI:SSOF'); to_char 2023-11-27T11:21:37-03 ) -- Álvaro HerreraBreisgau, 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)
Re: [DOCS] Add example about date ISO format
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
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 20da3ed033..8a83ac0529 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8748,6 +8748,12 @@ SELECT regexp_match('abc01234xyz',
'(?:(.*?)(\d+)(.*)){1,1}');
to_char(current_timestamp,
'FMDay, FMDD HH12:MI:SS')
'Tuesday, 6 05:39:18'
+
+to_char(current_timestamp AT TIME ZONE
+'UTC', '-MM-DD"T"HH24:MI:SSZ')
+'2022-12-06T05:39:18Z',
+ISO 8601 extended format
+
to_char(-0.1, '99.99')
' -.10'
Re: [DOCS] Add example about date ISO format
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
Re: [DOCS] Add example about date ISO format
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 https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 93f068edcf..4c15eed66f 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8748,6 +8748,12 @@ SELECT regexp_match('abc01234xyz', '(?:(.*?)(\d+)(.*)){1,1}');
to_char(current_timestamp, 'FMDay, FMDD HH12:MI:SS')
'Tuesday, 6 05:39:18'
+
+to_char(current_timestamp AT TIME ZONE
+'UTC', '-MM-DD"T"HH24:MI:SSZ')
+'2022-12-06T05:39:18',
+ISO 8601 T format
+
to_char(-0.1, '99.99')
' -.10'
Re: [DOCS] Add example about date ISO format
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
Re: [DOCS] Add example about date ISO format
On Wed, Nov 22, 2023 at 12:26 PM Bruce Momjian 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.
Re: [DOCS] Add example about date ISO format
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 https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: [DOCS] Add example about date ISO format
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 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)
Re: [DOCS] Add example about date ISO format
On 2023-11-22 17:58 +0100, Bruce Momjian wrote: > On Wed, Nov 22, 2023 at 02:02:02PM +0100, Erik Wienhold wrote: > > > > + > > > > + > > > > +to_char(current_timestamp AT TIME ZONE 'UTC', > > > > +'-MM-DD"T"HH24:MI:SSZ') 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: > > -MM-DD"T"HH24:MI:SSOF or -MM-DD"T"HH24:MI:SSTZH:TZM > > > > Fractional seconds are also possible: -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 ISO 8601 date/time format. > > > > + > > > > + > > > > + > > > > > > > > > > > > > > > > > > +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
Re: [DOCS] Add example about date ISO format
On Wed, Nov 22, 2023 at 02:02:02PM +0100, Erik Wienhold wrote: > > > + > > > + > > > +to_char(current_timestamp AT TIME ZONE 'UTC', > > > +'-MM-DD"T"HH24:MI:SSZ') 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: > -MM-DD"T"HH24:MI:SSOF or -MM-DD"T"HH24:MI:SSTZH:TZM > > Fractional seconds are also possible: -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 ISO 8601 date/time format. > > > + > > > + > > > + > > > > > > > > > > > > > +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 https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index e4a7b07033..4943f63871 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -2472,7 +2472,11 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02' input, but on output it uses a space rather than T, as shown above. This is for readability and for consistency with https://tools.ietf.org/html/rfc3339";>RFC 3339 as - well as some other database systems. + well as some other database systems. The function call + to_char(current_timestamp AT TIME ZONE 'UTC', + '-MM-DD"T"HH24:MI:SSZ') outputs the current + UTC date/time in ISO 8601 format with + T.
Re: [DOCS] Add example about date ISO format
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 +, juha.musto...@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(), '-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}');
> >
> >
> >
> > +
> > +
> > +to_char(current_timestamp AT TIME ZONE 'UTC',
> > +'-MM-DD"T"HH24:MI:SSZ') 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:
-MM-DD"T"HH24:MI:SSOF or -MM-DD"T"HH24:MI:SSTZH:TZM
Fractional seconds are also possible: -MM-DD"T"HH24:MI:SS,FF6
> > +date/time in ISO 8601 date/time format.
> > +
> > +
> > +
> >
> >
> >
>
> +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
Re: [DOCS] Add example about date ISO format
On Tue, 2023-11-21 at 23:33 -0500, Bruce Momjian wrote: > On Fri, Feb 17, 2017 at 04:01:54PM +, juha.musto...@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(), '-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
Re: [DOCS] Add example about date ISO format
On Fri, Feb 17, 2017 at 04:01:54PM +, juha.musto...@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(), '-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 https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
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}');
+
+
+to_char(current_timestamp AT TIME ZONE 'UTC',
+'-MM-DD"T"HH24:MI:SSZ') outputs the current UTC
+date/time in ISO 8601 date/time format.
+
+
+
