Tom Lane said:
"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)."
Erm, what do you mean by an interval? If you mean a number, then its broken, because "UTC+10" and "+10" do the same thing. But you seem to be saying there is indeed some syntax that is interpreted by ISO logic?
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
