Inconsistency of timezones in postgresql

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

Responses

Browse pgsql-bugs by date

  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