Re: Inconsistency of timezones in postgresql - Mailing list pgsql-bugs
| From | Chris BSomething |
|---|---|
| Subject | Re: Inconsistency of timezones in postgresql |
| Date | |
| Msg-id | CADrHaBHn32Ot-vziDQcCvVtjaw9Qd9Kp8A0jYmnEHyiNrffE4g@mail.gmail.com Whole thread Raw |
| In response to | Re: Inconsistency of timezones in postgresql (Aleksander Alekseev <aleksander@timescale.com>) |
| Responses |
Re: Inconsistency of timezones in postgresql
|
| List | pgsql-bugs |
The quote is from here:
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...
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@timescale.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
pgsql-bugs by date: