7.2 Beta timezone woes

Elein <elein@nextbus.com> writes:

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.

Huh?

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.

If you think this makes the clients' job harder rather than easier,
then you're either thinking about it all wrong or you have a very
peculiar set of requirements. Perhaps you could explain why the above
mind-set doesn't work for you.

regards, tom lane

Tom Lane wrote:

Elein <elein@nextbus.com> writes:

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.

Huh?

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.

If you think this makes the clients' job harder rather than easier,
then you're either thinking about it all wrong or you have a very
peculiar set of requirements. Perhaps you could explain why the above
mind-set doesn't work for you.

regards, tom lane

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.

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.

Perhaps part of the solution is to input the time as timetz.
Perhaps another is to store the display timezone separately.

Or I may be thinking of this all wrong :-) How would one
display multiple timezones easily in one application?
Any brilliant ideas would be great.

Or maybe I should write a new timestamp_fixedtz type :-)

Thanks,
elein

--
--------------------------------------------------------
elein@nextbus.com
(510)420-3120
www.nextbus.com
spinning to infinity, hallelujah
--------------------------------------------------------

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

Thanks, you all. I was able to finesse the problem
by isolating the calculation and display in a subprocess
of the client which let me set PGTZ based on the timezone
recorded separately for the dataset.

We, here, brought up the idea of storing the timestamps
w/o timezones and I'm not sure, but that may be the broader
solution.

thanks

elein

Thomas Lockhart wrote:

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--
--------------------------------------------------------
elein@nextbus.com
(510)420-3120
www.nextbus.com
spinning to infinity, hallelujah
--------------------------------------------------------