TIMESTAMP problem in GnuCash Postgresql backend

[Matthew Vanecek]

If you read nothing else, please read the 2nd-to-last paragraph.  I may
have written more than was necessary....

On Sun, 2003-08-03 at 13:54, Derek Atkins wrote:
> Matthew Vanecek <mevanecek at yahoo.com> writes:
> 
> Let's say that you get the string "2003-07-25 00:00:00-05" but you're
> in the US/Pacific timezone -- how would that get displayed?
> 

>From which backend? And in current state or desired state?  And are you
storing the date to the backend or retrieving it?

> Now, what about if you got the gmt string string "2003-07-25 05:00:00"
> but you're in the US/Pacific timezone?  How would _THAT_ get
> displayed?
> 
> I would assert that:
> 
> a) if you're in the US/Eastern timezone, both of these timestamps would
>    be displayed exactly the same, and
> 2) if you're in the US/Pacific timezone, both of these timestamps would
>    STILL be displayed exactly the same.
> 3) If you're in any other timezone, both of these timestamps would be
>    displayed "correctly" by converting to the local timezone.
> 

Well, Here's what would be displayed based on the Eastern time zone for
your fist case:

me2v at reliant tmp $ TZ='US/Eastern' ./gncdate "2003-07-25 05:00:00"
calling gnc_iso_8601_to_timespec_local(2003-07-25 05:00:00)
        local tz date string  : 2003-07-25 01:00:00.000000 -0400
calling gnc_iso8601_to_timespec_gmt(2003-07-25 05:00:00)
        GMT tz date string    : 2003-07-25 05:00:00.000000 -0400

and for the second string (including time zone):
me2v at reliant tmp $ TZ='US/Eastern' ./gncdate "2003-07-25 05:00:00-05"
calling gnc_iso_8601_to_timespec_local(2003-07-25 05:00:00-05)
        local tz date string  : 2003-07-25 06:00:00.000000 -0400
calling gnc_iso8601_to_timespec_gmt(2003-07-25 05:00:00-05)
        GMT tz date string    : 2003-07-25 10:00:00.000000 -0400

So while the *dates* are the same (due to the time and offset), the
timestamps are different when you include/disinclude the time zone info
in the string.

> There is absolutely no ambiguity in a GMT timestamp -- you just need
> to convert back and forth from GMT to localtime at the "border".  In
> fact, you could just use gnc_iso8601_to_timespec_gmt() to convert the
> string (in GMT format) to a Timespec.  The only problem is that there
> is (currently) no API to _print_ an iso8601 time in GMT format.
> 

Actually, this function is performed by the "TIMESTAMP WITH TIME ZONE"
data type.  A "TIMESTAMP WITH TIME ZONE" field presents the stored date
in the local time zone.

> See my previous message about why storing with a timezone can be
> problematic.  Yes, just ignoring the timezone is worse -- there is
> definitely something that needs to be fixed here.  However I feel the
> easiest thing to do is just store the data in GMT, and convert it
> back and forth in the backend.
> 

I have no problem with the GMT storage.  Postgresql will convert from
the stored time zone to your server's local time zone when you select
the date field from the database, if you use "WITH TIME ZONE" (which
means you really don't need external conversion routines when retrieving
data).  You just have to be sure to provide it with a correct time-zoned
date on input.  Keep in mind that the timestamp Postgresql provides is
based on the *server's* time zone, not the client's.

So, here are some questions:
1) Why is this field a timestamp in the first place, instead of a simple
date (just curious, here)?
2) Is the engine going to use time-zone specific dates, and expect the
backend to convert back-n-forth to/from GMT?
3) If you move from Mass. to Australia, do you expect the times/dates of
historical transactions to be presented in Australian time?  Wouldn't
that be a bit confusing?


In the grand scheme of things, this is not that major of an issue.  My
feel is that because time zone info is stored in the venerable File
backend, it should also be stored in the Postgresql backend.  I am 100%
certain, however, that regardless of backend meanderings, when I enter
07/25/2003 in the register, I expect to see 07/25/2003 in the register
ad infinitum, until I change that data myself in the register.  I would
also like to point out: The only reason the file backend does *not* have
the same bug as the PG backend is because the file backend stores the
timezone info.  If you change the time zone in a ts:date field to, say,
+0000 (in my case), or +0100, etc., then the date in that node will
display as the previous date.  This is the same behavior being exhibited
by the PG backend.  Try it for yourself...

Also, please note that if I send my xac file to, say, Honolulu, and
someone in Honolulu opened my file unaltered, all the dates in the
transaction register would display as the day before, instead of the day
stored in the file.  Just setting TZ to Honolulu produces the same
effect: TZ='Pacific/Honolulu' gnucash finances.xac

In the current implementation, the date in the PG backend *is not
GMT*--it is local time as entered in the register.  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.

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.

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.

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