[SQL] At time zone madness!

[John Goss]

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