Postgres backend and Timezones

[Derek Atkins]

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.

> My question in general was not *whether* the PG backend should be
> changed. Rather, I'm more concerned about whether an upgrade path should
> be provided.  Linas indicates yes, and since that's the direction I was
> leaning already....

Yes, all SQL backends should provide an upgrade path (and the existing
one does).

> My next question is, during the upgrade path, should I rely on
> Postgresql extensions to SQL, or use (as close to as possible)
> SQL99-compliant SQL only.  Some of the Postgresql extensions make the
> upgrade a bit easier, so I'm leaning that direction--especially since
> it's the *postgres* backend. ;)

Well, it depends whether you're trying to use the existing code or
write a "libdbi" version...  If this is just extension of the existing
code then sure, feel free to use PG-specific extensions.

-derek

-- 
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       warlord at MIT.EDU                        PGP key available