Re: Assigning a timestamp without timezone to a timestamp with timezone

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(at)crankycanuck(dot)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