Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id D6C629FB43A
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 17 Oct 2006 00:27:47 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 84874-09 for <pgsql-sql-postgresql.org@postgresql.org>;
	Tue, 17 Oct 2006 00:27:43 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from talk.nabble.com (www.nabble.com [72.21.53.35])
	by postgresql.org (Postfix) with ESMTP id 0E8C09FB414
	for <pgsql-sql@postgresql.org>; Tue, 17 Oct 2006 00:27:42 -0300 (ADT)
Received: from [72.21.53.38] (helo=jubjub.nabble.com)
	by talk.nabble.com with esmtp (Exim 4.50) id 1GZfcE-00066j-5N
	for pgsql-sql@postgresql.org; Mon, 16 Oct 2006 20:27:42 -0700
Message-ID: <6847852.post@talk.nabble.com>
Date: Mon, 16 Oct 2006 20:27:42 -0700 (PDT)
From: chrisj <chrisj.wood@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Assigning a timestamp without timezone to a timestamp
In-Reply-To: <7622.1160969464@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Nabble-From: chrisj.wood@sympatico.ca
References: <6613652.post@talk.nabble.com>
	<20061003135623.GB3191@phlogiston.dyndns.org>
	<6621346.post@talk.nabble.com>
	<20061003145134.GA3399@phlogiston.dyndns.org>
	<8C5B026B51B6854CBE88121DBF097A864DE848@ehost010-33.exch010.intermedia.net>
	<20061005183128.GD8826@phlogiston.dyndns.org>
	<6667446.post@talk.nabble.com>
	<20061005203121.GI8826@phlogiston.dyndns.org>
	<6815181.post@talk.nabble.com> <13338.1160868392@sss.pgh.pa.us>
	<6827636.post@talk.nabble.com> <7622.1160969464@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.204 tagged_above=0 required=5 tests=AWL,
	SPF_HELO_PASS, SPF_PASS
X-Spam-Level: 
X-Archive-Number: 200610/155
X-Sequence-Number: 26544


Thanks Tom that's great!!

When I first saw your solution I thought it was logically going to do
(notice the parentheses):
    select ('2006-07-13 09:20:00'::timestamp) at time zone 'EST5EDT';  
which does not help

So I was not hopeful, but when I tried it it did exactly what I needed which
is:
    select '2006-07-13 09:20:00'::(timestamp at time zone 'EST5EDT');  

My adjusted SQL is:

select start_datetime
  , cast(start_datetime as timestamp(0) without time zone)::timestamp at
time zone B.timezone_ch
   from reservation A
          , location B 
    where  A.appt_key = 7 
       and B.locn_key = 102;

thank-you so much


Tom Lane-2 wrote:
> 
> chrisj <chrisj.wood@sympatico.ca> writes:
>> Did not seem to help:
>> ERROR:  invalid input syntax for type timestamp with time zone:
>> "2006-07-13
>> 09:20:00 America/New_York"
> 
> Sorry, I was thinking in terms of CVS HEAD ... the ability to use a full
> timezone spec in timestamptz input is new for 8.2.  You might be able to
> use this, which does work in 8.1:
> 
> select '2006-07-13 09:20:00'::timestamp at time zone 'EST5EDT';   
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
> 
> 

-- 
View this message in context: http://www.nabble.com/Assigning-a-timestamp-without-timezone-to-a-timestamp-with-timezone-tf2373845.html#a6847852
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.