Re: Inconsistency of timezones in postgresql

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?

On Wed, 31 Jul 2024 at 23:34, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> 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
>