Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g03FhXm44986 for ; Thu, 3 Jan 2002 10:43:33 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g03FhVg12913; Thu, 3 Jan 2002 10:43:31 -0500 (EST) To: "Tom Kreiner" cc: pgsql-sql@postgresql.org Subject: Re: Getting Hour From a Time in Different Time Zone In-reply-to: References: Comments: In-reply-to "Tom Kreiner" message dated "Wed, 02 Jan 2002 16:45:57 -0500" Date: Thu, 03 Jan 2002 10:43:30 -0500 Message-ID: <12910.1010072610@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200201/17 X-Sequence-Number: 6108 "Tom Kreiner" writes: > SELECT hour(timestamp(current_timestamp AT TIME ZONE INTERVAL '-05 hours')); The AT TIME ZONE construct produces a text string, which you are converting back into a timestamp, which is internally just GMT --- all notion that it had anything to do with EST time is gone. You can actually get the above to work in 7.2, though. It looks like Thomas changed AT TIME ZONE to not emit timezone in the output string, which avoids the rotation back to your own zone: regression=# SELECT current_timestamp; timestamptz ------------------------------- 2002-01-03 10:40:42.901964-05 (1 row) regression=# SELECT current_timestamp AT TIME ZONE INTERVAL '-08 hours'; timezone ---------------------------- 2002-01-03 07:40:49.452058 (1 row) regression=# SELECT "timestamp"(current_timestamp AT TIME ZONE INTERVAL '-08 hours'); timestamp ---------------------------- 2002-01-03 07:41:19.469686 (1 row) regression=# SELECT extract(hour from "timestamp"(current_timestamp AT TIME ZONE INTERVAL '-08 hours')); date_part ----------- 7 (1 row) regards, tom lane