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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Aleksander Alekseev <aleksander(at)timescale(dot)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
>