The input timestamp is noon. Turn it into a string. Concatenate “Australia/Sydney” to it. Cast that to timestamptz. Then rotate that 12pm Sydney time to UTC - resulting in 2AM. Print 2am to the screen with a “+00” suffix to indicate that what you are seeing is a timestamptz value displayed in your UTC specified time zone.
I.e. your just wrote something similar to (in common terms):
Select ‘2012-07-06 12:59:55+10’::timezone at time zone UTC
David J.
So what you are saying is that results for timestamp fields will be the exact opposite of timestamptz fields... i.e. AT TIME ZONE Australia/Sydney will be earlier than UTC for timestamp vs later than UTC for timestamptz
That seems like a terrible idea....
It also means that it is assuming dates are in the user's time zone.
but... according to the wiki: "Storing UTC values in a timestamp without time zone column is, unfortunately, a practice commonly inherited from other databases that lack usable timezone support."
I agree that people using this data type are attempting to store UTC in there. When should you use timestamp according to the wiki?
"If compatibility with non-timezone-supporting databases trumps all other considerations."
So if you want to store UTC timestamps for compatibility, use timestamp... but the database does the opposite, it assumes you're storing localtime, and then when you go AT TIME ZONE UTC, it goes the wrong direction.
Anyway, isn't the documentation wrong?
timestamp without time zone AT TIME ZONE zone → timestamp with time zone
Converts given time stamp without time zone to time stamp with time zone, assuming the given value is in the named time zone.
timestamp '2001-02-16 20:38:40' at time zone 'America/Denver' → 2001-02-17 03:38:40+00
Unfortunately the doco doesn't tell us what environmental time zone it is assuming so the user is not enlightened by the examples.
It says that it assumes that the "value is in the NAMED timezone". What actually happens is it assumes the value is in your environmental time zone, and DISPLAYS it in your current zone.
In any case, nobody could read the documentation and not be utterly confused when they see what postgresql actually does.
