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 B7F3011F8C7A for ; Thu, 20 Apr 2006 13:20:53 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.124 X-Spam-Level: X-Spam-Status: No, score=0.124 required=5 tests=[AWL=0.124] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 2AFgtLoHM6NA for ; Thu, 20 Apr 2006 13:20:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id E5C2C11F6B72 for ; Thu, 20 Apr 2006 13:20:38 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k3KGKYfx019392; Thu, 20 Apr 2006 12:20:34 -0400 (EDT) To: "John Goss" cc: pgsql-sql@postgresql.org Subject: Re: At time zone madness! In-reply-to: <2f2cb1690604200631l47c2a5b4xabe92bb6d22b4f2c@mail.gmail.com> References: <2f2cb1690604200631l47c2a5b4xabe92bb6d22b4f2c@mail.gmail.com> Comments: In-reply-to "John Goss" message dated "Thu, 20 Apr 2006 14:31:20 +0100" Date: Thu, 20 Apr 2006 12:20:34 -0400 Message-ID: <19391.1145550034@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200604/143 X-Sequence-Number: 24952 "John Goss" 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