Message-ID: <6863766.post@talk.nabble.com>
Date: Tue, 17 Oct 2006 13:42:17 -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: <16546.1161092042@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
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>
	<6847852.post@talk.nabble.com> <16546.1161092042@sss.pgh.pa.us>


Hi Tom,

Thanks again, I did not appreciate the dual function of "AT TIME ZONE"  when
the input is timestamptz then the function converts from one timezone to
another (not what I wanted),

but when the input is timestamp the function acts more like a cast than a
convert (exactly what I wanted)

I must disagree with your assertion about the redundancy of:
>   , cast(start_datetime as timestamp(0) without time zone)::timestamp at
> time zone B.timezone_ch
 
what I am doing is taking a timestamptz, discarding its timezone, and then
casting it to another timezone

for example from  2006-10-03 09:00:00 NZST  to  2006-10-03 09:00:00 EST5EDT

If I am missing a much easier way to accomplish this please let me know.


Tom Lane-2 wrote:
> 
> chrisj <chrisj.wood@sympatico.ca> writes:
>> 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
> 
> Well, actually, that's exactly what it does.  AT TIME ZONE is an
> operator that converts timestamp without time zone to timestamp with
> time zone (or vice versa).  I guess you could easily get confused
> here, but AT is not WITH.
> 
>>   , cast(start_datetime as timestamp(0) without time zone)::timestamp at
>> time zone B.timezone_ch
> 
> That's redundant --- you're casting the result of the cast to timestamp
> (implicitly without time zone), then applying the AT TIME ZONE operator.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
> 
>                http://archives.postgresql.org
> 
> 

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