Обсуждение: 7.2 Beta timezone woes

When I store a timestamptz with a non-client timezone,
it is stored in my table converted GMT.
Then, every access to it is in my client timezone, including
extract( timezone from value).

I want to be able to access the timestamps with the
timezone information I input it with.

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.
(timzone() requires that we know whether it was day light savings
or not.)  Or before and after the query set time zone which
eliminates having to know about day light savings. But
I don't want the whole session to be in the timezone of
the one row of data.  I want the timestamp to know its
timezone.

Am I missing something or trying to make it do something
too clever?

thanks for your help,

elein@nextbus.com

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

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
--------------------------------------------------------