Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m0D39-0002fa-Rj for pgsql-docs@arkaria.postgresql.org; Mon, 05 Jul 2021 01:05:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1m0D38-0004xX-N3 for pgsql-docs@arkaria.postgresql.org; Mon, 05 Jul 2021 01:05:26 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m0D38-0004xP-Gv for pgsql-docs@lists.postgresql.org; Mon, 05 Jul 2021 01:05:26 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1m0D33-0008Qx-SJ for pgsql-docs@lists.postgresql.org; Mon, 05 Jul 2021 01:05:25 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 16515JUT1387832; Sun, 4 Jul 2021 21:05:19 -0400 From: Tom Lane To: ysangkok@gmail.com cc: pgsql-docs@lists.postgresql.org Subject: Re: Second-granular timezone offset format not documented In-reply-to: <162543102827.697.5755498651217979813@wrigleys.postgresql.org> References: <162543102827.697.5755498651217979813@wrigleys.postgresql.org> Comments: In-reply-to PG Doc comments form message dated "Sun, 04 Jul 2021 20:37:08 -0000" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <1387800.1625447090.0@sss.pgh.pa.us> Date: Sun, 04 Jul 2021 21:05:19 -0400 Message-ID: <1387831.1625447119@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1387800.1625447090.1@sss.pgh.pa.us> PG Doc comments form 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 ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="doc-fractional-minute-offsets.patch"; charset="us-ascii" Content-ID: <1387800.1625447090.2@sss.pgh.pa.us> Content-Description: doc-fractional-minute-offsets.patch Content-Transfer-Encoding: quoted-printable 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 04:05:06.789-8 - ISO 8601 + ISO 8601, with time zone as UTC offset 04:05:06-08:00 - ISO 8601 + ISO 8601, with time zone as UTC offset 04:05-08:00 - ISO 8601 + ISO 8601, with time zone as UTC offset 040506-08 - ISO 8601 + ISO 8601, with time zone as UTC offset + + + 040506+0730 + ISO 8601, with fractional-hour time zone as UTC offset + + + 040506+07:30:00 + UTC offset specified to seconds (not allowed in ISO 8601= ) 04:05:06 PST @@ -2068,17 +2076,21 @@ MINUTE TO SECOND PST8PDT POSIX-style time zone specification + + -8:00:00 + UTC offset for PST + -8:00 - ISO-8601 offset for PST + UTC offset for PST (ISO 8601 extended format) -800 - ISO-8601 offset for PST + UTC offset for PST (ISO 8601 basic format) -8 - ISO-8601 offset for PST + UTC offset for PST (ISO 8601 basic format) zulu @@ -2086,7 +2098,7 @@ MINUTE TO SECOND z - Short form of zulu + Short form of zulu (also in ISO 8601)= @@ -2395,6 +2407,24 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02' = + + In the ISO style, the time zone is always shown a= s + a signed numeric offset from UTC, with positive sign used for zones + east of Greenwich. The offset will be shown + as hh (hours only) if it is an integral + number of hours, else + as hh:mm if it + is an integral number of minutes, else + hh:mm:ss. + (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 + (hh or hhmm). + + In the SQL and POSTGRES styles, day appears befor= e month if DMY field ordering has been specified, otherwise month appe= ars ------- =_aaaaaaaaaa0--