Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1sZBLz-00GwN6-TP
	for pgsql-bugs@arkaria.postgresql.org; Wed, 31 Jul 2024 15:35:03 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1sZBLx-009QaR-Vu
	for pgsql-bugs@arkaria.postgresql.org; Wed, 31 Jul 2024 15:35:01 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1sZBLx-009QVs-My
	for pgsql-bugs@lists.postgresql.org; Wed, 31 Jul 2024 15:35:01 +0000
Received: from sss.pgh.pa.us ([68.162.161.243])
	by makus.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <tgl@sss.pgh.pa.us>)
	id 1sZBLv-002OpO-AI
	for pgsql-bugs@lists.postgresql.org; Wed, 31 Jul 2024 15:35:00 +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 46VFYvGh3387162;
	Wed, 31 Jul 2024 11:34:57 -0400
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Aleksander Alekseev <aleksander@timescale.com>
cc: Chris BSomething <xpusostomos@gmail.com>, pgsql-bugs@lists.postgresql.org
Subject: Re: Inconsistency of timezones in postgresql
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>
Comments: In-reply-to Aleksander Alekseev <aleksander@timescale.com>
	message dated "Wed, 31 Jul 2024 16:23:20 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3387160.1722440097.1@sss.pgh.pa.us>
Date: Wed, 31 Jul 2024 11:34:57 -0400
Message-ID: <3387161.1722440097@sss.pgh.pa.us>
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Archived-At: 
 <https://www.postgresql.org/message-id/3387161.1722440097%40sss.pgh.pa.us>
Precedence: bulk

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