> >>The way it is puts the burden entirely on the client to figure out
> >>what timezone the data is for and force the appropriate
> >>timezone( 'MST', value) formatting to it for arithmetic and display.
> > It seems like you are entirely missing the point. The idea is that
> > the client storing a time value presents it in his local timezone;
> > the internal storage is an *absolute* time (independent of any timezone
> > ... the fact that the internal representation is GMT is merely a remnant
> > of 18th-century British imperialism); and any client who asks for the
> > value gets it presented in *his* local timezone.
> With a client in california, I want to do (timestamptz - time)
> where both values are "in MST' and display the results and the
> timestamptz in MST time. While still having my client set
> to PST.
So in this case (which may be simpler than your actual application) the
time zone information is not really used at all, right? At least
internally; maybe the new client cares what time zone was used for the
calculation?
> I have times from various locations that I want to
> display in their own timezone. I only know what their
> timeszones are when I input them.
You *might* want to store a timestamp with out time zone and a character
string time zone as a separate field. Or you could store the timestamp
with time zone and a separate character field for the time zone of
original data entry (I like this better).
You can convert back and forth to different time zones (mostly intended
for display purposes) by using the timezone() function:
thomas=# set time zone 'PST8PDT';
SET VARIABLE
thomas=# select timestamp 'now', timezone('EST', timestamp 'now') || ' '
|| 'EST';
timestamptz |
?column?
-------------------------------------+-------------------------------------
Mon Jan 14 16:31:13.724333 2002 PST | Mon Jan 14 19:31:13.724333 2002
EST
You can also use extract('timezone' from xxx) to get ahold of a numeric
time zone offset, but matching that back up with a stringy offset is not
obvious.
> Or maybe I should write a new timestamp_fixedtz type :-)
I'm not sure that the range of math and display options you want could
be magically fixed by using a single new type. You still have a data
conversion issue between time zones, and a representation issue if you
want to use "stringy time zones" rather than numeric time zone offsets.
hth
- Thomas
