Re: At time zone madness!

"John Goss" <mrjohngoss(at)gmail(dot)com> writes:
> My problem is that when trying to SELECT a timestamp using "AT TIME ZONE
> 'Europe/London", it's subtracting an hour instead of adding it from a column
> storing UTC dates.

I think you've got a misunderstanding about the behavior of AT TIME ZONE.
It's really two distinct operations depending on whether the input is a
timestamp with or without timezone.

1) If input is timestamp with timezone: rotate the time to what it would
be locally in the specified timezone, and output that as timestamp without
timezone.

2) If input is timestamp without timezone: interpret the given time as
being local time in the specified timezone, and output as timestamp WITH
timezone. Internally, that means rotating to UTC because timestamp with
timezone is always stored as UTC internally.

So in fact one case adds the UTC offset of the given timezone, and the
other case subtracts it.

I suspect that you should be storing your posttime column as timestamp
with timezone, not without. In general, if a stored timestamp value is
meant to represent an absolute time instant, timestamp with tz is the
correct datatype for it. Timestamp without tz isn't very well-defined.

If you wanted to stick with timestamp-without-tz as the column datatype,
having the convention that it's always represented in UTC, then the
correct way to derive the equivalent time in some other zone would be

SELECT posttime AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/London'

The first AT TIME ZONE has the effect of specifying what the stored
timestamp really means, and the second does the rotation to London time.

regards, tom lane