Message-ID: <6621346.post@talk.nabble.com>
Date: Tue, 3 Oct 2006 07:26:52 -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: <20061003135623.GB3191@phlogiston.dyndns.org>
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>


Hi Andrew,

I do appreciate your reply and we agree on two things timestamp without
timezone should be avoided and the timestamps in table 2 should definately
be "with timezone".

I have no problem changing the timestamps in table 1 to "with timezone", but
I do not see how this solves my problem (maybe I am just thick).

the timestamps in table 1 are not the open and close times for the corporate
location, but they are the directive to all store locations saying: "In the
context of the timezone your store is located in,  these are the hours you
should be open.

For example the corporate office may be on the east coast and they are
saying that on December 24,2006 you should open at 9am and close at 1pm. 
Stores in California should open at 9:00am Pacific time and stores in New
York should open at 9am EDT.

If I did not appreciate the full implication of your answer please be
patient with me sometimes I am slow but I usually get there.


Andrew Sullivan wrote:
> 
> On Mon, Oct 02, 2006 at 08:15:56PM -0700, chrisj wrote:
>> Two tables both the have open and close columns that are timestamp or
>> timestamp with time zone.
> 
> I think the best answer is to convert the one table to timestamptz,
> and always enter explicitly the time zone with it (since you're going
> to know the corporate timezone anyway, right?).  This way, you don't
> have to worry about the client's timezone setting, and you always get
> the right answer.  For instance:
> 
> test=# SHOW TimeZone ;
>  TimeZone 
> ----------
>  EST5EDT
> (1 row)
> 
> test=# SELECT '2006-10-03 09:00:00-00'::timestamptz;
>       timestamptz       
> ------------------------
>  2006-10-03 05:00:00-04
> (1 row)
> 
> This has the other advantage that if an office moves, its "open time"
> in history doesn't need to change, and you don't need external
> knowledge about what the office time zone is, because that's encoded
> in the timestamp.
> 
> In general, I think timestamps without timezones are just a bad
> idea.
> 
> A
> 
> -- 
> Andrew Sullivan  | ajs@crankycanuck.ca
> I remember when computers were frustrating because they *did* exactly what 
> you told them to.  That actually seems sort of quaint now.
> 		--J.D. Baldwin
> 
> ---------------------------(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#a6621346
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.