Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 8EB479FA4A5 for ; Tue, 5 Dec 2006 17:06:45 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 09067-05 for ; Tue, 5 Dec 2006 17:06:33 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 137CE9FA173 for ; Tue, 5 Dec 2006 17:06:40 -0400 (AST) 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 kB5L6b07011136; Tue, 5 Dec 2006 16:06:37 -0500 (EST) To: "Collin Peters" cc: pgsql-sql@postgresql.org Subject: Re: Question about "AT TIME ZONE" In-reply-to: References: Comments: In-reply-to "Collin Peters" message dated "Tue, 05 Dec 2006 12:29:26 -0800" Date: Tue, 05 Dec 2006 16:06:37 -0500 Message-ID: <11135.1165352797@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/58 X-Sequence-Number: 27014 "Collin Peters" writes: > In the first example it says it is converted to PST "for display". In > the second example it is not converted to PST for display. Does this > mean that if a timestamp *with* a timezone is specified, and it also > includes "AT TIME ZONE", that it is not converted to PST "for display" > at the end? AT TIME ZONE does two different things that are sort of inverses of each other: it can convert timestamp without timezone to timestamp with timezone, or the reverse. In the case where you're converting to ts with tz, there's an additional step involved, which is converting the UTC-base ts-with-tz value to your current TimeZone for display. That happens when SELECT emits the value as text, not as part of AT TIME ZONE. AT TIME ZONE itself basically either adds or subtracts the UTC offset of the named time zone, thus converting a value in UTC to or from a value that's relative to the named zone. regards, tom lane