Обсуждение: Second-granular timezone offset format not documented

The following documentation comment has been logged on the website:

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

I would like to request additional documentation on the timezone format that
can be returned.
Context: I had a problem with the HDBC-postgresql library, it cannot parse
the second-granular timezone offsets returned by PostgreSQL.

Here is an example of a format that I don't think the documentation
currently covers:
janus=> set timezone to 'America/Mexico_City';
SET
janus=> select '1920-12-25' :: timestamptz;
         timestamptz          
------------------------------
 1920-12-25 00:00:00-06:36:36
(1 row)

Note how the response has a very weird timezone offset. I guess it is valid,
but the documentation should note this as an edge case, since timezone
offsets are typically only in minutes.

Thank you for your patience.

PG Doc comments form <noreply@postgresql.org> writes:
> Here is an example of a format that I don't think the documentation
> currently covers:
> janus=> set timezone to 'America/Mexico_City';
> SET
> janus=> select '1920-12-25' :: timestamptz;
>          timestamptz          
> ------------------------------
>  1920-12-25 00:00:00-06:36:36
> (1 row)

Yeah, fair point.  There's a passing mention of fractional-minute
offsets in Appendix B, but the main docs don't cover it at all.
I propose the attached draft patch.

            regards, tom lane

diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index c473d6a746..bd3bf5768c 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -2020,19 +2020,27 @@ MINUTE TO SECOND
          </row>
          <row>
           <entry><literal>04:05:06.789-8</literal></entry>
-          <entry>ISO 8601</entry>
+          <entry>ISO 8601, with time zone as UTC offset</entry>
          </row>
          <row>
           <entry><literal>04:05:06-08:00</literal></entry>
-          <entry>ISO 8601</entry>
+          <entry>ISO 8601, with time zone as UTC offset</entry>
          </row>
          <row>
           <entry><literal>04:05-08:00</literal></entry>
-          <entry>ISO 8601</entry>
+          <entry>ISO 8601, with time zone as UTC offset</entry>
          </row>
          <row>
           <entry><literal>040506-08</literal></entry>
-          <entry>ISO 8601</entry>
+          <entry>ISO 8601, with time zone as UTC offset</entry>
+         </row>
+         <row>
+          <entry><literal>040506+0730</literal></entry>
+          <entry>ISO 8601, with fractional-hour time zone as UTC offset</entry>
+         </row>
+         <row>
+          <entry><literal>040506+07:30:00</literal></entry>
+          <entry>UTC offset specified to seconds (not allowed in ISO 8601)</entry>
          </row>
          <row>
           <entry><literal>04:05:06 PST</literal></entry>
@@ -2068,17 +2076,21 @@ MINUTE TO SECOND
           <entry><literal>PST8PDT</literal></entry>
           <entry>POSIX-style time zone specification</entry>
          </row>
+         <row>
+          <entry><literal>-8:00:00</literal></entry>
+          <entry>UTC offset for PST</entry>
+         </row>
          <row>
           <entry><literal>-8:00</literal></entry>
-          <entry>ISO-8601 offset for PST</entry>
+          <entry>UTC offset for PST (ISO 8601 extended format)</entry>
          </row>
          <row>
           <entry><literal>-800</literal></entry>
-          <entry>ISO-8601 offset for PST</entry>
+          <entry>UTC offset for PST (ISO 8601 basic format)</entry>
          </row>
          <row>
           <entry><literal>-8</literal></entry>
-          <entry>ISO-8601 offset for PST</entry>
+          <entry>UTC offset for PST (ISO 8601 basic format)</entry>
          </row>
          <row>
           <entry><literal>zulu</literal></entry>
@@ -2086,7 +2098,7 @@ MINUTE TO SECOND
          </row>
          <row>
           <entry><literal>z</literal></entry>
-          <entry>Short form of <literal>zulu</literal></entry>
+          <entry>Short form of <literal>zulu</literal> (also in ISO 8601)</entry>
          </row>
         </tbody>
        </tgroup>
@@ -2395,6 +2407,24 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
      </para>
     </note>

+    <para>
+     In the <acronym>ISO</acronym> style, the time zone is always shown as
+     a signed numeric offset from UTC, with positive sign used for zones
+     east of Greenwich.  The offset will be shown
+     as <replaceable>hh</replaceable> (hours only) if it is an integral
+     number of hours, else
+     as <replaceable>hh</replaceable>:<replaceable>mm</replaceable> if it
+     is an integral number of minutes, else
+     <replaceable>hh</replaceable>:<replaceable>mm</replaceable>:<replaceable>ss</replaceable>.
+     (The last case is not possible with any modern time zone standard,
+     but it can appear when working with timestamps that predate the
+     introduction of standardized time zones.)
+     In the other date styles, the time zone is shown as an alphabetic
+     abbreviation if one is in common use in the current zone; otherwise
+     it appears as a signed numeric offset in ISO 8601 basic format
+     (<replaceable>hh</replaceable> or <replaceable>hhmm</replaceable>).
+    </para>
+
     <para>
      In the <acronym>SQL</acronym> and POSTGRES styles, day appears before
      month if DMY field ordering has been specified, otherwise month appears

On Mon, Jul 5, 2021 at 9:56 AM PG Doc comments form
<noreply@postgresql.org> wrote:
> Note how the response has a very weird timezone offset. I guess it is valid,

As for whether it's valid, that's coming from the IANA tz dataset.  It
has a moment that it believes standard time to have begun at each
location, in this case:

Z America/Mexico_City -6:36:36 - LMT 1922 Ja 1 0:23:24

https://en.wikipedia.org/wiki/Time_in_Mexico#History seems to agree on
the year at least.  That "local mean time" offset is computed from the
location's longitude, for lack of anything better.  The tzinfo
"Theory" file has a bunch of disclaimers about pre-1970 data though,
including "the tz database's LMT offsets should not be considered
meaningful".

Thomas Munro <thomas.munro@gmail.com> writes:
> As for whether it's valid, that's coming from the IANA tz dataset.  It
> has a moment that it believes standard time to have begun at each
> location, in this case:
> Z America/Mexico_City -6:36:36 - LMT 1922 Ja 1 0:23:24
> https://en.wikipedia.org/wiki/Time_in_Mexico#History seems to agree on
> the year at least.  That "local mean time" offset is computed from the
> location's longitude, for lack of anything better.  The tzinfo
> "Theory" file has a bunch of disclaimers about pre-1970 data though,
> including "the tz database's LMT offsets should not be considered
> meaningful".

I tried to interest them in dropping the LMT idea altogether [1].
Unsurprisingly, the proposal went nowhere.

            regards, tom lane

[1] http://mm.icann.org/pipermail/tz/2021-May/030114.html

On Tue, Jul 6, 2021 at 2:04 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I tried to interest them in dropping the LMT idea altogether [1].

FWIW, I agree with you.  It's meaningless because those coordinates
don't seem to be the meridians historically used for local mean time
(Trafalgar Square may be the prime meridian for pigeons, but real
London time was based on its most famous observatory long before
standardisation AFAICS, and if even that "zero case" is wrong, I guess
the rest are wrong too where there even is an answer; the year given
is also disputable).  That's all fine and well given the disclaimer
that it's meaningless, but then why even have it?  The LMT concept is
itself being applied proleptically (before the definition of mean
time, before the existence of the named cities, ...).  I think it
would be a whole lot more useful and less surprising to make standard
time proleptic instead, or just reject undefined conversions.