pg.ddx.io pgsql-sql@postgresql.org mailing list archive help / color / mirror / Atom feed From: chrisj <chrisj.wood@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Assigning a timestamp without timezone to a timestamp
Date: Mon, 16 Oct 2006 20:27:42 -0700 (PDT)
Message-ID: <6847852.post@talk.nabble.com> (raw )
In-Reply-To: <7622.1160969464@sss.pgh.pa.us >
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 >
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....
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
view thread (24+ messages) latest in thread
Message-ID: <6847852.post@talk.nabble.com>
Permalink: ../6847852.post@talk.nabble.com/
Also on: postgresql.org/message-id/6847852.post@talk.nabble.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: chrisj.wood@sympatico.ca
Subject: Re: Assigning a timestamp without timezone to a timestamp
In-Reply-To: <6847852.post@talk.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox