From: Tom Lane <tgl@sss.pgh.pa.us>
To: Aleksander Alekseev <aleksander@timescale.com>
Cc: Chris BSomething <xpusostomos@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Inconsistency of timezones in postgresql
Date: Wed, 31 Jul 2024 11:34:57 -0400
Message-ID: <3387161.1722440097@sss.pgh.pa.us> (raw)
In-Reply-To: <CAJ7c6TME_YvU-zS0Jn1R6LECPvCamG4UToitZw7+0UJdDL+K0g@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>
Aleksander Alekseev <aleksander@timescale.com> writes:
> I don't see any mention of the standard. As I understand the
> documentation merely says that timezone() corresponds to the AT TIME
> ZONE SQL-syntax. Whether the syntax is standard or not is not clear.
The syntax has been there since SQL92:
6.14 <datetime value expression>
Function
Specify a datetime value.
Format
<datetime value expression> ::=
<datetime term>
| <interval value expression> <plus sign> <datetime term>
| <datetime value expression> <plus sign> <interval term>
| <datetime value expression> <minus sign> <interval term>
<datetime term> ::=
<datetime factor>
<datetime factor> ::=
<datetime primary> [ <time zone> ]
<datetime primary> ::=
<value expression primary>
| <datetime value function>
<time zone> ::=
AT <time zone specifier>
<time zone specifier> ::=
LOCAL
| TIME ZONE <interval value expression>
However, notice that the value following TIME ZONE is only allowed to
be an interval by the spec (and this is still true in SQL:2021,
the latest version I have handy). Such an interval is interpreted per
ISO (positive = east of Greenwich). We allow that too, but we extend
the standard to allow time zone names as well.
The problem comes down to a very ancient decision to allow POSIX
zone strings as time zone names. We are not entirely to blame on
that, because the code involved is borrowed from the IANA tzcode
distribution. If you experiment, you will find out that common
Unix utilities interpret TZ the same way:
$ psql
psql (18devel)
Type "help" for help.
regression=# select now() at time zone 'America/New_York';
timezone
----------------------------
2024-07-31 11:32:12.089097
(1 row)
regression=# select now() at time zone 'UTC+2';
timezone
----------------------------
2024-07-31 13:32:14.399523
(1 row)
regression=# \q
$ TZ=America/New_York date
Wed Jul 31 11:32:23 EDT 2024
$ TZ=UTC+2 date
Wed Jul 31 13:32:26 UTC 2024
So whether you like it or not, it's pretty standard behavior.
There is zero chance that we'll change it.
regards, tom lane
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: tgl@sss.pgh.pa.us, aleksander@timescale.com, xpusostomos@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Inconsistency of timezones in postgresql
In-Reply-To: <3387161.1722440097@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