Postgres backend and Timezones

[Alex Barclay]

Derek Atkins wrote:

>Matthew Vanecek <mevanecek at yahoo.com> writes:
>
>  
>
>>>IMHO, all dates should be stored in GMT and presented to local time
>>>for the viewer.  I.e., the user works in local time, the data is
>>>stored in GMT.  That the XML backend is adding a timezone is, IMHO, a
>>>bug that should be fixed.
>>>
>>>      
>>>
>>I agree with Linas.  Users will want to see stuff presented to them in
>>local time.  I personally want my data *stored* in local time, too,
>>which is how the engine currently gives it to the backend from the UI. 
>>The difference between the file and the postgres backend is that the
>>file backend actually stores the time zone information with the
>>timestamp, where the postgres backend doesn't. *That* is the bug, in the
>>postgres backend.
>>    
>>
>
>Well, we're all in agreement that users want to see stuff presented in
>local time.  I just don't see why the PG Backend can't store in GMT
>and convert to local when it reads?
>
>You may feel GMT is over-rated, but I think that storing data in a
>local timezone is more problematic.  At least when I'm living in some
>timezone I always know how to convert to GMT.  However I may have no
>clue how to convert from some South-Pacific Island timezone to GMT, so
>someone providing me a timestamp with that timezone is doing me no
>favors.
>
>  
>
I normally keep quiet on this list. Under absolutely no circumstances 
should you store data in a "local timezone" format. I have suffered way 
too many of these systems over the years and they are completely 
unworkable when multiple timezones come into use.

If you are born, live and are planning to die in one timezone then this 
would work very well for you but I travel between four timezones on a 
weekly basis.

Consider the following situation. I am on EST and my wife is on PST. If 
we both make changes using a multi user setup then which timezone should 
be stored? I am quite happy to store GMT then a note saying that the 
entry was made by someone who was in PST - I now know if the change was 
made before or after 5pm local but would be able to see it at the 
correct time for me.

Just my $0.02 worth. My suffering went back to a telecom system I 
developed at MCI back in the mid '90s. The bright folks at MCI decided 
that all times were relative to EST and all monetary values were going 
to be in USD. You can imagine the problems I had when I wanted to deploy 
this across 10 European countries!

Alex.