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