Bugzilla 90768

John Ashley Burgoyne burgoyne@sas.upenn.edu
14 Aug 2002 16:52:22 -0400

When I migrated my data from a GNC file to the SQL backend,
approximately 15% of the transactions started displaying one day
earlier than the date for which I had orginally entered them. I went
into the database directly (by way of psql) to check into the problem,
and discovered that these transactions were indeed listed as having
occurred a day too early--at 19:00:00 or 20:00:00 instead of
00:00:00--i.e. when gnctransaction.date_posted should have been
2002-08-11 00:00:00, it was 2002-08-10 20:00:00
instead. Unfortunately, I have been unable to find a pattern to
explain why only certain records were corrupted to begin with.  They
span all dates of entry and dates of posting.

I fixed the dates from within GnuCash, and the next time I opened
GnuCash, they had all moved back a day again. I re-checked the
database and confirmed that the timestamps were still wrong. I
repaired gnctransaction manually:

UPDATE gnctransaction
SET date_posted = date_trunc('day', date_posted) + '1 day'
WHERE to_char(date_posted, 'HH24:MI:SS') <> '00:00:00';

using psql, and GnuCash still displayed the dates one day early. I
repaired gnctransactionaudit using a similar command, and GnuCash
still displayed the dates one day too early. I saved the data in GNC
format again, check the timestamps for some problem transactions, and
sure enough, 2002-08-11 00:00:00 was still 2002-08-10 20:00:00. I
saved the file back into the database, and gnctransaction and
gnctransactionaudit still list correctly.

I have no idea where the incorrect timestamps are being stored, which
is why I have CCd this bug report to gnucash-user and
gnucash-devel. Does anybody have any workaround ideas or know where
else to sniff out incorrect timestamps?

Many thanks,

John Ashley Burgoyne

P.S. I use James Treacy's sid packages gnucash and gnucash-sql 1.6.6-3.