I've been trawling the net trying to find an answer to this, so sorry if this is a common problem - I have tried looking before posting!
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.
The session time zone is set to UTC, and the field is a timestamp without time zone.
However, if I do the same thing on now() instead of the result of my query, it works!
More details below:
johngoss_cs=> set session time zone 'UTC';
SET
=> select now();
now
-------------------------------
2006-04-20 13:19:04.585175+00
(1 row)
=> select now() at time zone 'Europe/London';
timezone
----------------------------
2006-04-20 14:19:12.535543
(1 row)
At the time of running this the time was 14:10 in the UK (13:10 UTC)
=> select posttime from tbldiscussionreplies where rid = 300284;
posttime
----------------------------
2006-04-20 13:10:51.160939
(1 row)
Ok, so this is the raw posttime - which should always be UTC. Works fine.
=> select posttime at time zone 'UTC' from tbldiscussionreplies where rid = 300284;
timezone
-------------------------------
2006-04-20 13:10:51.160939+00
(1 row)
Try getting it at UTC - again fine - the session time zone is set to UTC, so it doesn't change anything.
=> select posttime at time zone 'Europe/London' from tbldiscussionreplies where rid = 300284;
timezone
-------------------------------
2006-04-20 12:10:51.160939+00
(1 row)
The problem!
For some reason this has subtracted an hour - making it two hours wrong!
The field is described as:
posttime | timestamp without time zone | not null default timezone('utc'::text, now())
Any ideas?
Thanks!
John
