X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 42D2E11F6CD8
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 20 Apr 2006 10:31:30 -0300 (ADT)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Score: 0.001
X-Spam-Level: 
X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001]
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id c8wO7kYsxCZs
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 20 Apr 2006 10:31:18 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from nz-out-0102.google.com (nz-out-0102.google.com
 [64.233.162.207])
	by postgresql.org (Postfix) with ESMTP id 968F511F6CD6
	for <pgsql-sql@postgresql.org>; Thu, 20 Apr 2006 10:31:21 -0300 (ADT)
Received: by nz-out-0102.google.com with SMTP id m7so125624nzf
	for <pgsql-sql@postgresql.org>; Thu, 20 Apr 2006 06:31:20 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=Ec+zK0XuAJ+Luyd0Oke5eqmf2GoAdnT2lgutfhGiht5T+3u2o4UqFzmA4MRPoCzeQVOUnsNQHrkPNjQoce5oZXs++gAvm6umxF47XOtq9RadoBmyvirfJS3LpAAlxJJhMGNedvC2xyBttc4FtdfLGDyAD8muGNr2ocGpW+BPI0E=
Received: by 10.36.140.1 with SMTP id n1mr195296nzd;
	Thu, 20 Apr 2006 06:31:20 -0700 (PDT)
Received: by 10.36.158.10 with HTTP; Thu, 20 Apr 2006 06:31:20 -0700 (PDT)
Message-ID: <2f2cb1690604200631l47c2a5b4xabe92bb6d22b4f2c@mail.gmail.com>
Date: Thu, 20 Apr 2006 14:31:20 +0100
From: "John Goss" <mrjohngoss@gmail.com>
To: pgsql-sql@postgresql.org
Subject: At time zone madness!
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_8318_14095878.1145539880586"
X-Archive-Number: 200604/137
X-Sequence-Number: 24946

------=_Part_8318_14095878.1145539880586
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 colum=
n
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=3D> set session time zone 'UTC';
SET

=3D> select now();
              now
-------------------------------
 2006-04-20 13:19:04.585175+00
(1 row)

=3D> 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)


=3D> select posttime from tbldiscussionreplies where rid =3D 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.



=3D> select posttime at time zone 'UTC' from tbldiscussionreplies where rid=
 =3D
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, s=
o
it doesn't change anything.


=3D> select posttime at time zone 'Europe/London' from tbldiscussionreplies
where rid =3D 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

------=_Part_8318_14095878.1145539880586
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<div>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!</div>
<div>&nbsp;</div>
<div>My problem is that when trying to SELECT a timestamp using &quot;AT TI=
ME ZONE 'Europe/London&quot;, it's subtracting an hour instead of adding it=
 from a column storing UTC dates.</div>
<div>&nbsp;</div>
<div>The session time zone is set to UTC, and the field is a timestamp with=
out time zone. </div>
<div>&nbsp;</div>
<div>However, if I do the same thing on now() instead of the result of my q=
uery, it works!</div>
<div>&nbsp;</div>
<div>More details below:</div>
<div>&nbsp;</div>
<div>
<p><br>johngoss_cs=3D&gt; set session time zone 'UTC';<br>SET</p>
<p>=3D&gt; select now();<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; now<br>-------------------------------<br>&=
nbsp;2006-04-20 13:19:04.585175+00<br>(1 row)</p>
<p>=3D&gt; select now() at time zone 'Europe/London';<br>&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; timezone<br>--------------------------=
--<br>&nbsp;2006-04-20 14:19:12.535543<br>(1 row)</p>
<p>At the time of running this the time was 14:10 in the UK (13:10 UTC)</p>
<p><br>=3D&gt; select posttime from tbldiscussionreplies where rid =3D 3002=
84;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; posttime<br>-=
---------------------------<br>&nbsp;2006-04-20 13:10:51.160939<br>(1 row)<=
/p>
<p>Ok, so this is the raw posttime - which should always be UTC. Works fine=
.</p>
<p>&nbsp;</p>
<p>=3D&gt; select posttime at time zone 'UTC' from tbldiscussionreplies whe=
re&nbsp;rid =3D 300284;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp; timezone<br>-------------------------------<br>&nbsp;2006-04-2=
0 13:10:51.160939+00<br>(1 row)</p>
<p>Try getting it at UTC - again fine - the session time zone is set to UTC=
, so it doesn't change anything.</p>
<p><br>=3D&gt; select posttime at time zone 'Europe/London' from tbldiscuss=
ionreplies where rid =3D 300284;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp; timezone<br>-------------------------------<br>&nbsp;=
2006-04-20 12:10:51.160939+00<br>(1 row)</p>
<p>The problem!</p>
<p>For some reason this has subtracted an hour - making it two hours wrong!=
</p>
<p>&nbsp;</p>
<p>The field is described as:<br>&nbsp;posttime&nbsp;&nbsp;&nbsp;&nbsp;&nbs=
p;&nbsp; | timestamp without time zone | not null default timezone('utc'::t=
ext, now())</p>
<p><br>Any ideas?<br></p>
<p>Thanks!</p>
<p>John</p></div>

------=_Part_8318_14095878.1145539880586--