Message-ID: <6613652.post@talk.nabble.com>
Date: Mon, 2 Oct 2006 20:15:56 -0700 (PDT)
From: chrisj <chrisj.wood@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Assigning a timestamp without timezone to a timestamp with timezone
MIME-Version: 1.0
Content-Type: text/plain; charset=ANSI_X3.4-1968
Content-Transfer-Encoding: 7bit


Hi
Does any one have any ideas for the following problem?

Two tables both the have open and close columns that are timestamp or
timestamp with time zone.

One row in first table represents the corporate office default open and
close times for all stores relative to the store?s own time zone for a
particular day.  

The second table represents the specific open and close time for a specific
store for a specific day, occasionally a store?s hours can be different from
the corporate default.

Table1:
open_time   timestamp
close_time   timestamp

Table2:
store_number   int
open_time    timestamp with timezone
close_time   timestamp with timezone


I would like to be able to initialize table 2 from table 1.

Suppose I had a store table that contained

Store_table:
Store_number   int
Store_tz             char(03)

I would like to do something like:

Insert into Table2
Select S.store_number 
  ,cast(T1.open_time  as timestamp with timezone at S.Store_tz)
  ,cast(T1.close_time as timestamp with timezone at S.Store_tz)
from Store_table S,  Table1 T1


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