TIMESTAMP problem in GnuCash Postgresql backend

[Derek Atkins]

Matthew Vanecek <mevanecek at yahoo.com> writes:

To answer a question that I removed, we store a timestamp because we
SHOULD.  (Timestamps happen at a particular time, not a particular
day).  See bug #89439.

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

>  When it is
> retrieved, it is 'converted' to local time (even though it already is,
> but w/o a time zone).  This causes the register to display the incorrect
> date.  This is why the PG Backend needs to store time zone information
> just like the File backend.

The File Backend is broken, too -- it should also have stored data in
GMT.  Mirroring the behavior of a broken (venerable, and EOLed) format
is not doing anyone any favors.

> I'm not trying to debate the rightness/wrongness of time zone info, or
> how we should or shouldn't store date information.  I really don't care
> about that.  All I'm trying to do is have the PG backend store data with
> the same attributes as the XML backend (e.g., time zone) as closely as
> possible, and to ensure that the behavior on the same data is the same
> in the PG backend as it is in the XML backend.  If you or someone else
> wants to change the way the XML backend stores date info, that's
> fine--I'll be happy to adjust the PG backend accordingly to match. 
> Until that time, however, the PG backend needs to store the time zone,
> in order to match with how the XML backend presents dates to the engine.

That's fine.  Here's what you do:

When storing a date in the PG Backend:

        convert localtime to gmt
        store the (GMT timestamp) on the server

When retreiving a date from the PG Backend:

        obtain the date (in GMT) from the server
        convert from gmt to localtime

> To change the PG backend to handle time zones properly is relatively
> minor. To change the entire application to use GMT and only convert to
> local when displaying in the register/reports, well, that's a little
> more complicated, and probably not worth the effort.  Especially
> considering all the other projects going on right now.

I'm just sugesting that you change the PG backend to handle GMT
properly.  Yea, the pie-in-the-sky answer is to handle all dates in
GMT until presented to the user, but that's probably not going to
happen.  So, the next best thing is to store all dates in GMT in the
data files.  The XML backend sort-of does this (albeit in a broken way
by including the tz offset), the PG backend does not (hense the bug).

PLEASE PLEASE PLEASE do not try to mirror the broken behavior of the
XML backend.  It's doing nobody any favors.

-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