From: Tom Lane <tgl@sss.pgh.pa.us>
To: ysangkok@gmail.com
Cc: pgsql-docs@lists.postgresql.org
Subject: Re: Second-granular timezone offset format not documented
Date: Sun, 04 Jul 2021 21:05:19 -0400
Message-ID: <1387831.1625447119@sss.pgh.pa.us> (raw)
In-Reply-To: <162543102827.697.5755498651217979813@wrigleys.postgresql.org>
References: <162543102827.697.5755498651217979813@wrigleys.postgresql.org>
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
Attachments:
[text/x-diff] doc-fractional-minute-offsets.patch (3.9K, ../1387831.1625447119@sss.pgh.pa.us/2-doc-fractional-minute-offsets.patch)
download | inline diff:diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgmlindex 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
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: tgl@sss.pgh.pa.us, ysangkok@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: Second-granular timezone offset format not documented
In-Reply-To: <1387831.1625447119@sss.pgh.pa.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