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:
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
