TIMESTAMP problem in GnuCash Postgresql backend

[Derek Atkins]

Matthew Vanecek <mevanecek at yahoo.com> writes:

> > > In the current implementation, the date in the PG backend *is not
> > > GMT*--it is local time as entered in the register.  
> > 
> > And I maintain that *THIS* is the problem.  If the PG backend stored
> > the timestamp in GMT we wouldn't have an issue (with the PG Backend).
> > 
> 
> I probably should have said, the PG timestamp does not have an
> associated time zone.  It is not GMT nor any other.  No matter what your
> local TZ is, that field will always display the same date on a select,
> unlike a timestamp with time zone field.

That's fine -- so long as I provide "2003-07-25 03:00:00" then I
always get back "2007-07-25 03:00:00", regardless of timezone changes
on the client or server, that's sufficient.  The "invariant" is that
the timestamp is a GMT timestamp, and it's the client's responsibility
to store the timestamp in GMT and convert it to localtime upon reading
it.

However, if it's at all possible to have a postgres configuration
where I store a timestamp as "2003-07-25 03:00:00" and get back
"2003-07-25 02:00:00" then I agree with you that we need to use the
"timestamp with time zone" data type.

[snip]
> That works.  The columns should still be redefined to include time zone,
> though.  The fact that we expect to store the timestamp in GMT time
> still suggests using the "time zone" clause, and we may need it in the
> future.  Also, timestamp and timestamp with time zone are both 8 bytes
> (and SQL standard), so we don't lose storage space that way.  It also 
> merges your last step into one step, since the database would perform
> the gmt->localtime conversion.  If the backend can be 100% sure that the
> engine is providing a local TZ date, then it's reasonable to implement
> it your way.

both 8 bytes?!?  weird..  i wonder how they do that?

> Let me point out that doing the conversion means the behavior of dates
> displaying differently in significantly different time zones will be
> carried forward.  The only way around *that* is to dispense with time
> zones totally, and use the data as it is stored in the backend (meaning
> "2003-08-01 00:00:00" is "2003-08-01 00:00:00" no matter what your time
> zone is, because that's what's in the data store).  As long as everyone
> understands and accepts that, then I'm satisfied with the conversion
> path (not that there's a quick fix for it, anyhow!).

That's fine.  Here's a case in point.  I do something at 2003-08-01
23:00:00 US/Pacific.  When does that event happen for someone in
US/Eastern?

Personally I think part of the problem is storing dates at midnight,
rather than noon.

Besides, when you run the upgrade (from a client) you'll certainly
know the client's timezone at that point and just assume that timezone
when you convert the timestamps.

> Additionally, it presents a convenient opportunity to change gncEntry to
> gncSplit....

That would be nice ;)

-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