From: | Chris BSomething <xpusostomos(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Inconsistency of timezones in postgresql |
Date: | 2024-07-31 10:55:50 |
Message-ID: | CADrHaBFMWsMxGoPBPqY570HSDQZeJ3BtJHhSbUg5LFdErZX6ug@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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, until I found this buried in the documentation:
" 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."
So apparently Postgres supports 2 completely contradictory standards,
depending on what you're doing. (And I might add, this isn't even mentioned
on the "at time zone" doco page.)
I'm guessing it was just too easy to take the easy (but wrong) decision to
support posix time zones because a lot of Linux C functions and machinery
was sitting there and too easy to piggy back on. But it can't be a good
idea.
Anyway, surely postgres should pick one of these standards and at least
support it everywhere, presumably the ISO one which everyone understands. I
could suggest a number of ways of doing this without annoying people with
incompatibility, but may I suggest that "UTC" is a posix promoted syntax,
and ISO promotes "Z" to mean UTC:
https://en.wikipedia.org/wiki/ISO_8601
So might I suggest that AT TIME ZONE "Z+10:00" should be interpreted in ISO
fashion, like we all expect, and "UTC-10:00" can remain as the "weird"
posix format. Then you can promote the Z format more extensively in the
documentation so that normal people aren't drawn into confusion land.
Chris
From | Date | Subject | |
---|---|---|---|
Next Message | Aleksander Alekseev | 2024-07-31 11:11:06 | Re: BUG #18556: Parallel operation error during CREATE TABLE AS statement |
Previous Message | Sandeep Thakkar | 2024-07-31 09:53:42 | Re: [EXTERNAL] Re: Windows Application Issues | PostgreSQL | REF # 48475607 |