TIMESTAMP problem in GnuCash Postgresql backend

[Matthew Vanecek]

There exists an annoyance in the Gnucash Postgresql backend related to
the way the tables are created.  Specifically, the date/time fields are
created as a TIMESTAMP data type.  In Postgresql, a TIMESTAMP does not
include time zone information.  The Postgresql backend uses the
gnc_iso8601_to_timespec_local() function to convert the date retrieved
from the database to a Timespec object.

During the conversion, Gnucash recognizes correctly that the timestamp
string contains no time zone information and therefore adjusts the
timestamp to reflect the current time zone.  That is, of course, correct
behavior.  However, it causes (for example) "2003-07-25 00:00:00.000000"
to be rendered to "2003-07-24 19:00:00.000000 -0500", and 07/24/03
displays in the register instead of 07/25/03.

Now, the fix may seem simple--use the gnc_iso8601_to_timespec_gmt()
function instead.  While that may yield correct results in the short
term, it is an incorrect solution.  I base that on the fact that the
file backend includes time zone information in all timestamps in the
file.  Thus, when a file is being saved to the PG backend, the time zone
information is being discarded.  That, to me, is undesirable.

The correct fix is to change the definition of the TIMESTAMP columns to
"TIMESTAMP WITH TIME ZONE".  This option yields correct results, and is
a correct fix.

The problem for me is what to do with people that already actively use
the Postgresql backend (if any).  There should be an upgrade path to
change existing TIMESTAMP to the "TIME ZONE" format.  This is not a
trivial operation, but I *can* do it, by renaming the existing column,
adding a new column with the original name, moving the data to the new
column, and dropping the original column.

Do y'all think that we should worry about that?  As far as I can tell,
the data is stored properly in the database in all cases--it appears
that it is just the presentation that is messed up. Opinions?

-- 
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...
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: This is a digitally signed message part
Url : /pipermail/attachments/20030731/db6ae717/attachment.bin