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 →
-
doc: add example of sign mismatch with POSIX/ISO-8601 time zones
- 06dc1ffd2409 18.0 landed
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