From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Aleksander Alekseev <aleksander@timescale.com>
To: pgsql-bugs@lists.postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Chris BSomething <xpusostomos@gmail.com>
Subject: Re: Inconsistency of timezones in postgresql
Date: Wed, 29 Jan 2025 14:09:09 +0100
Message-ID: <eb4d1e15c6822c1937be1491118500dd9201492f.camel@cybertec.at> (raw)
In-Reply-To: <CAJ7c6TMJJR0ubWxcNG_KhzfE1Q2JUHeWFezgcWUUO=B7KRHjVQ@mail.gmail.com>
References: <CADrHaBFMWsMxGoPBPqY570HSDQZeJ3BtJHhSbUg5LFdErZX6ug@mail.gmail.com>
<CAJ7c6TNdDV5iE4x7Zw5Ceqt84p97kVYdouWCB4jxYJcyzZpyQg@mail.gmail.com>
<CADrHaBHn32Ot-vziDQcCvVtjaw9Qd9Kp8A0jYmnEHyiNrffE4g@mail.gmail.com>
<CAJ7c6TMpU_0NrLMsW0e0TTyfps8TY2g9d5t62kVWkAw_8ARm1Q@mail.gmail.com>
<CADrHaBE9EykgvqWQnm0wV7eZdzXjS0G+YZNpZxqpqEXXduwQdA@mail.gmail.com>
<CAJ7c6TME_YvU-zS0Jn1R6LECPvCamG4UToitZw7+0UJdDL+K0g@mail.gmail.com>
<CADrHaBEV+rAH9samE7cEPv=AGjFj175JT_ncatvYLt4FfyXD6w@mail.gmail.com>
<CAJ7c6TNrxqZVNx2ob2-x5wMP3WHtyR6xKUZJjNuKFNuHs6XMsA@mail.gmail.com>
<3384710.1722438948@sss.pgh.pa.us>
<CAJ7c6TMJJR0ubWxcNG_KhzfE1Q2JUHeWFezgcWUUO=B7KRHjVQ@mail.gmail.com>
On Thu, 2024-08-01 at 13:24 +0300, Aleksander Alekseev wrote:
> > I think this is a documentation issue, specifically that Section 8.5.3
> > is not sufficiently in-your-face about "UTC+2" not meaning what you
> > probably think. We didn't really do anybody any favors by shoving
> > those details off to Appendix B.
>
> IMO the warning should be placed closer to the documentation for AT
> TIME ZONE where users have more chances to find it. Here is the patch.
I think it is odd to have a second set of examples in the documentation
of AT TIME ZONE. My attached patch instead adds an example with
explanation to the already existing examples and a nore to the data
types section.
What do you think?
Yours,
Laurenz Albe
Attachments:v2-0001-Document-POSIX-time-zone-sign-oddity-more-visibly.patch (text/x-patch, 3.4K) download
From 17aaf075727940c5ca7e7dc07572d67f60935846 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@cybertec.at>
Date: Wed, 29 Jan 2025 14:05:46 +0100
Subject: [PATCH v2] Document POSIX time zone sign oddity more visibly
Appendix B already mentioned that the sign of the time zone offset
in POSIX time zones is the opposite of the normal time zone offset
sign. However, this was somewhat hidden, so add a note to the
data type section and an example and a reference to the documentation
of AT TIME ZONE.
Reported by Chris BSomething.
Author: Aleksander Alekseev
Author: Laurenz Albe
Discussion: https://postgr.es/m/CADrHaBFMWsMxGoPBPqY570HSDQZeJ3BtJHhSbUg5LFdErZX6ug%40mail.gmail.com
---
doc/src/sgml/datatype.sgml | 8 ++++++++
doc/src/sgml/func.sgml | 9 +++++++--
2 files changed, 15 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgmlindex 1d9127e94e4..74cc4d8afca 100644--- a/doc/src/sgml/datatype.sgml+++ b/doc/src/sgml/datatype.sgml@@ -2679,6 +2679,14 @@ TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'
savings was nominally in effect on that date.
</para>
+ <note>+ <para>+ The sign in POSIX-style time zone specifications has the opposite meaning+ of the sign in ISO-8601 datetime values. For example, the POSIX time zone+ for <literal>2014-06-04 12:00+04</literal> would be UTC-4.+ </para>+ </note>+
<para>
To complicate matters, some jurisdictions have used the same timezone
abbreviation to mean different UTC offsets at different times; for
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgmlindex 5678e7621a5..b5f2f210026 100644--- a/doc/src/sgml/func.sgml+++ b/doc/src/sgml/func.sgml@@ -10968,6 +10968,8 @@ SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'Asia/Tokyo' AT TIME ZONE 'A
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 05:38:40</computeroutput>
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT LOCAL;
<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 17:38:40</computeroutput>
+SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE '+05';+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 20:38:40</computeroutput>
SELECT TIME WITH TIME ZONE '20:38:40-05' AT LOCAL;
<lineannotation>Result: </lineannotation><computeroutput>17:38:40</computeroutput>
</screen>
@@ -10980,11 +10982,14 @@ SELECT TIME WITH TIME ZONE '20:38:40-05' AT LOCAL;
Tokyo time to Chicago time. The fourth example shifts the time stamp
with time zone value to the time zone currently specified by the
<varname>TimeZone</varname> setting and returns the value without a
- time zone.+ time zone. The fifth example demonstrates that the sign in a POSIX-style+ time zone specification has the opposite meaning of the sign in an+ ISO-8601 datetime literal, as described in <xref linkend="datatype-timezones"/>+ and <xref linkend="datetime-appendix"/>.
</para>
<para>
- The fifth example is a cautionary tale. Due to the fact that there is no+ The sixth example is a cautionary tale. Due to the fact that there is no
date associated with the input value, the conversion is made using the
current date of the session. Therefore, this static example may show a wrong
result depending on the time of the year it is viewed because
--
2.48.1
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-bugs@postgresql.org
Cc: laurenz.albe@cybertec.at, aleksander@timescale.com, pgsql-bugs@lists.postgresql.org, tgl@sss.pgh.pa.us, xpusostomos@gmail.com
Subject: Re: Inconsistency of timezones in postgresql
In-Reply-To: <eb4d1e15c6822c1937be1491118500dd9201492f.camel@cybertec.at>
* 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