From: John Goss <mrjohngoss@gmail.com>
To: pgsql-sql@postgresql.org
Subject: At time zone madness!
Date: Thu, 20 Apr 2006 14:31:20 +0100
Message-ID: <2f2cb1690604200631l47c2a5b4xabe92bb6d22b4f2c@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mrjohngoss@gmail.com
Subject: Re: At time zone madness!
In-Reply-To: <2f2cb1690604200631l47c2a5b4xabe92bb6d22b4f2c@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox