Re: Inconsistency of timezones in postgresql

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