TIMESTAMP problem in GnuCash Postgresql backend

[Matthew Vanecek]

On Sun, 2003-08-03 at 19:12, Derek Atkins wrote:
> 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.
> 

Okie-doke.

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

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

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.

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

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

-- 
Matthew Vanecek
perl -e 'print $i=pack(c5,(41*2),sqrt(7056),(unpack(c,H)-2),oct(115),10);'
********************************************************************************
For 93 million miles, there is nothing between the sun and my shadow except me.
I'm always getting in the way of something...