SELECT TIMESTAMP WITH TIME ZONE ... AT TIME ZOME as inverted meaning with UTC times...
Hi,
From the documentation:
# SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
timezone
---------------------
2001-02-16 18:38:40
MST is UTC-07, so i would expect that i can replace MST with UTC-07, but:
# SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME
ZONE 'UTC-07';
timezone
---------------------
2001-02-17 08:38:40
The time returned is at UTC+07....
Lets try at UTC+07 instead:
# SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME
ZONE 'UTC+07';
timezone
---------------------
2001-02-16 18:38:40
I this a bug or a feature, and if it is a feature, whats the rational behind it?
Regards
Andreas
"Andreas Schultz" <andreas.schultz@gmail.com> writes:
MST is UTC-07, so i would expect that i can replace MST with UTC-07, but:
# SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME
ZONE 'UTC-07';
timezone
---------------------
2001-02-17 08:38:40
The time returned is at UTC+07....
A time zone name in that form is a POSIX-spec timezone specification,
and the POSIX spec says that positive is west from Greenwich.
Everywhere else in Postgres we follow the SQL spec, which says that
positive is east from Greenwich. Aren't standards wonderful?
regards, tom lane