Re: Inconsistency of timezones in postgresql

The quote is from here:

https://www.postgresql.org/docs/8.3/datatype-datetime.html

I guess if it's no longer even in the documentation, that's even worse.

The AT TIME ZONE syntax is mentioned here... with little detail, certainly
without warning you of this confusion...

https://www.postgresql.org/docs/17/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT

Yes, the issue is, the input expects hours east of GMT and the output is
hours west of GMT. The former is Posix, the latter is ISO.

I was referring to the AT TIME ZONE syntax, I'm sure there are other places
like you say the timezone() function.

I suggested that using "Z" for ISO interpretation (hours west of GMT) would
be minimal compatibility damage because the Posix (hours east of GMT)
documentation doesn't mention Z for UTC, it says things like UTC+10.

I don't think merely thinking of a new function name is good enough because
"AT TIME ZONE" I believe is an SQL standard. Now I don't have the SQL
standard because I don't want to shell out the crazy money they want for
it, but from my research, I don't believe the SQL standard says what format
the time zone should be in, so you are stuck with the situation of
inventing some string format that both accepts the current common things
people use, and also accepts something more sensible, aka ISO formats. On
the bright side, not that many people use this AT TIME ZONE feature, the
people who do use it for the most part will use geographic names, whIch
makes much more sense for serious use ( e.g. America/New_York ), the few
people who use offsets will be saying UTC-10 or whatever... which I would
argue leaves Z+10 as something that wouldn't interfere with anyone, yet
would give people an ISOish style AND substance for their zone offsets, and
we can deprecate UTC+- for normal people's use. And if there's one guy out
there using Z+- and expecting Posix, tough luck... should have read the
documentation.

On Wed, 31 Jul 2024 at 19:50, Aleksander Alekseev <aleksander(at)timescale(dot)com>
wrote:

> Hi,
>
> > So I basically wasted a day's work trying to figure out what was going
> on, with queries like this:
> >
> > select change_time at time zone 'UTC+10' from mytable;
> >
> > and getting nonsense [...]
>
> I couldn't understand the bug report at first. Apparently the
> complaint is about the following behavior:
>
> ```
> -- note: MSK is UTC+3
> -- as expected
> SELECT ('2024-07-31 12:34:56 MSK' :: timestamptz) AT TIME ZONE 'MSK';
> timezone
> ---------------------
> 2024-07-31 12:34:56
>
> -- as expected
> SELECT ('2024-07-31 12:34:56+3' :: timestamptz) AT TIME ZONE 'MSK';
> timezone
> ---------------------
> 2024-07-31 12:34:56
>
> -- as expected
> SELECT timezone('MSK', '2024-07-31 12:34:56+3');
> timezone
> ---------------------
> 2024-07-31 12:34:56
>
> -- nonsense
> SELECT ('2024-07-31 12:34:56+3' :: timestamptz) AT TIME ZONE '+3';
> timezone
> ---------------------
> 2024-07-31 06:34:56
>
> -- nonsense
> SELECT timezone('+3', '2024-07-31 12:34:56+3');
> timezone
> ---------------------
> 2024-07-31 06:34:56
> ```
>
> Same for AT TIME ZONE 'UTC+3' | 'GMT+3' | 'Z+3'. I agree this is
> inconsistent and counterintuitive. On the flip side changing this
> behavior would mean breaking backward compatibility.
>
> Maybe we could come up with a good name for a function that would
> replace timezone() and recommend using it instead.
>
> Thoughts?
>
> > " Another issue to keep in mind is that in POSIX time zone names,
> positive offsets are used for locations west of Greenwich. Everywhere else,
> PostgreSQL follows the ISO-8601 convention that positive timezone offsets
> are east of Greenwich."
>
> This quote seems to be from the previous versions of the
> documentation. Unless I missed something this behavior of timezone() /
> AT TIME ZONE is currently not documented.
>
> --
> Best regards,
> Aleksander Alekseev
>