Inconsistency of timezones in postgresql

Chris BSomething <xpusostomos@gmail.com>

From: Chris BSomething <xpusostomos@gmail.com>
To: pgsql-bugs@lists.postgresql.org
Date: 2024-07-31T10:55:50Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →
  1. doc: add example of sign mismatch with POSIX/ISO-8601 time zones

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