Re: 7.2 Beta timezone woes - Mailing list pgsql-general

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