GNOME Bugzilla – Bug 795405
All Dates in Price Database Off-By-One After 3.0 Upgrade
Last modified: 2018-06-30 00:08:41 UTC
All dates are now displayed as one day prior to the correct value in the Price Database window, after upgrading from 2.6.19 to 3.0. For example, I purchased shares on 03/28/2018. This date remains correct in the account ledger, but in the Price Database, it shows 03/27/2018, user:price, transaction. If I click the Edit button, the same incorrect date is displayed. This also affects all of the dates I manually entered and show source user:price-editor. I'm afraid to touch the Price Database now because I don't know if it will do more harm than good to try to manually edit these values. This makes the Price Database and related features somewhat unusable now. :(
What timezone?
The system is in Eastern Daylight Time Or as Windows calls it, Eastern Time (US & Canada)
Current raw data for the example given above: <price:time> <ts:date>2018-03-27 23:00:00 -0500</ts:date> </price:time> Raw data as seen in a pre-upgrade backup file: <price:time> <ts:date>2018-03-28 00:00:00 -0400</ts:date> </price:time>
For curiosity, I tried opening the backup file in GnuCash. It appears the price date values are being corrupted upon open. However, if I manually change the date from this: <ts:date>2018-03-28 00:00:00 -0400</ts:date> to this: <ts:date>2018-03-28 00:00:00 -0500</ts:date> Then everything works as expected during open and save. No corruption and no changes to the dates in that case.
Good work! So it would seem to be a DST issue: The TZ thinks that 3/28 wasn't DST. What offset is on prices stored today? If it's correct (i.e. -4) can you tell when it changed?
No, it's not correct. It's storing all values at -0500 as described above. The local offset is -4. If I add a price and save it right now... <price:time> <ts:date>2018-04-20 00:00:00 -0500</ts:date> </price:time>
Here is a DST changeover example. Backup file: <price:time> <ts:date>2016-03-14 00:00:00 -0400</ts:date> </price:time> <price:time> <ts:date>2016-03-11 00:00:00 -0500</ts:date> </price:time> After upgrade: <price:time> <ts:date>2016-03-13 23:00:00 -0500</ts:date> </price:time> <price:time> <ts:date>2016-03-10 23:00:00 -0600</ts:date> </price:time>
I'm seeing a similar (the same?) problem but for a different timezone. I'm in Australia (GMT+10, GMT+11 in daylight saving). All dates in the price database for price entries that came from a transaction entered in a register have been converted, during the 3.0 upgrade (from 2.6.16), to the date of the previous day, regardless of whether the original date was daylight saving or not. Price DB records that came from Finance::Quote do NOT show this conversion problem. Here is an example of a price record that was NOT correctly converted: 2.6.16: <price> <price:id type="guid">ef7e6401555b66dd905d53d217ecf3d4</price:id> <price:commodity> <cmdty:space>ASX</cmdty:space> <cmdty:id>ANZ.AX</cmdty:id> </price:commodity> <price:currency> <cmdty:space>ISO4217</cmdty:space> <cmdty:id>AUD</cmdty:id> </price:currency> <price:time> <ts:date>2017-10-03 00:00:00 +1100</ts:date> </price:time> <price:source>user:split-register</price:source> <price:type>transaction</price:type> <price:value>29695323/1000000</price:value> </price> 3.0: <price> <price:id type="guid">ef7e6401555b66dd905d53d217ecf3d4</price:id> <price:commodity> <cmdty:space>ASX</cmdty:space> <cmdty:id>ANZ.AX</cmdty:id> </price:commodity> <price:currency> <cmdty:space>CURRENCY</cmdty:space> <cmdty:id>AUD</cmdty:id> </price:currency> <price:time> <ts:date>2017-10-02 23:00:00 +1000</ts:date> </price:time> <price:source>user:split-register</price:source> <price:type>transaction</price:type> <price:value>29695323/1000000</price:value> </price> Here is an example of a price record that WAS correctly converted (or at least the date shown is the same): 2.6.16: <price> <price:id type="guid">2aa676b9b234f035cb1407e9da1a28dd</price:id> <price:commodity> <cmdty:space>ASX</cmdty:space> <cmdty:id>ANZ.AX</cmdty:id> </price:commodity> <price:currency> <cmdty:space>ISO4217</cmdty:space> <cmdty:id>AUD</cmdty:id> </price:currency> <price:time> <ts:date>2017-10-06 15:59:00 +1100</ts:date> </price:time> <price:source>Finance::Quote</price:source> <price:type>last</price:type> <price:value>294100000/10000000</price:value> </price> 3.0: <price> <price:id type="guid">2aa676b9b234f035cb1407e9da1a28dd</price:id> <price:commodity> <cmdty:space>ASX</cmdty:space> <cmdty:id>ANZ.AX</cmdty:id> </price:commodity> <price:currency> <cmdty:space>CURRENCY</cmdty:space> <cmdty:id>AUD</cmdty:id> </price:currency> <price:time> <ts:date>2017-10-06 14:59:00 +1000</ts:date> </price:time> <price:source>Finance::Quote</price:source> <price:type>last</price:type> <price:value>294100000/10000000</price:value> </price> Here is an example of a date that is NOT in daylight saving time, that was also NOT correctly converted: 2.6.16: <price> <price:id type="guid">6d2c05268ac11ce95c408a013a0c3bc8</price:id> <price:commodity> <cmdty:space>ASX</cmdty:space> <cmdty:id>BEN.AX</cmdty:id> </price:commodity> <price:currency> <cmdty:space>ISO4217</cmdty:space> <cmdty:id>AUD</cmdty:id> </price:currency> <price:time> <ts:date>2016-09-30 00:00:00 +1000</ts:date> </price:time> <price:source>user:price</price:source> <price:type>transaction</price:type> <price:value>10040000/1000000</price:value> </price> 3.0: <price> <price:id type="guid">6d2c05268ac11ce95c408a013a0c3bc8</price:id> <price:commodity> <cmdty:space>ASX</cmdty:space> <cmdty:id>BEN.AX</cmdty:id> </price:commodity> <price:currency> <cmdty:space>CURRENCY</cmdty:space> <cmdty:id>AUD</cmdty:id> </price:currency> <price:time> <ts:date>2016-09-29 23:00:00 +0900</ts:date> </price:time> <price:source>user:price</price:source> <price:type>transaction</price:type> <price:value>10040000/1000000</price:value> </price> Hopefully, this bug can be progessed from status NEEDINFO now.
Almost. One more question: Chris, were you testing on Windows or Linux?
Hi John, Windows 10.
I was able to get this up and running by hacking together an XML file that contained the backup of my price database, with all the time offsets changed using search and replace so that they match the "wrong" offsets being used by 3.0. That's a substantial pain, but I figure if it matches what's being saved by the new version, it'll have to be brought forward in whatever patch is developed anyway. FWIW, time offsets should not be saved for values that do not have a time component. This is a case of garbage in, garbage out.
(In reply to Robert Chapin from comment #11) > I was able to get this up and running by hacking together an XML file that > contained the backup of my price database, with all the time offsets changed > using search and replace so that they match the "wrong" offsets being used > by 3.0. That's a substantial pain, but I figure if it matches what's being > saved by the new version, it'll have to be brought forward in whatever patch > is developed anyway. > > FWIW, time offsets should not be saved for values that do not have a time > component. This is a case of garbage in, garbage out. It was an early design decision to use unix time (seconds since midnight GMT 1 Jan 1970) to represent both dates and times. As you note this has some problems with timezones, see e.g. bug 137017. However, that's not always the case with prices. Prices can have times, though user prices generally don't. It's probably worthwhile to apply the same fix as we did for posted_date: Use 10:59 GMT instead of midnight. That affords much less sensitivity to timezone. That's separate from this bug, though: In this case it turns out that I'd made a couple of mistakes reading the time zone information from the Windows Registry, with the result that the DST offset was sign-reversed and the DST start and end dates were also reversed. The net effect was that the time zone was always shifted an hour west. That's now fixed and will be reflected in tomorrow's nightly (https://code.gnucash.org/builds/win32/maint). Please test. I haven't written a scrub, I'm not sure if that's needed. Robert has already corrected his file. I think that the saved times are OK, the problem is just that they're interpreted wrong.
Hi John, New version is fine now thanks very much. <ts:date> fields after they are upgraded are now exactly as they were in my 2.6.16 data file. I agree you don't need a scrub. I don't think it really matters if historical price database records have date off by 1 day.
Hmm. I actually hope that once you re-save the file the dates will have reverted to their correct values, as long as you didn't already change them.
I not sure I understand your last comment. I'll have to test in about 7 hours as busy now sorry.
Chris, what I mean is that 00:00 +10:00 and 23:00 +9:00 are the same time, so with the TZ corrected I think that when you save your file with a fixed version the new file will have the right timestamps even for previously retrieved prices.
Hi John, Ahh I see. You mean a scrub is not needed because the next time I save, it will adjust the time as well as the offset back to what it should be. I have tested using the 3.0 nightly build from 28/4/2018: 1) resaving the 3.0 database that had the incorrect price dates 2) upgrading my 2.6.16 database to 3.0 All good thanks. Dates (<ts:date> fields) are back to exactly how they were in 2.6.16. No scrub is needed.
Chris, Great! Thanks for testing.
GnuCash bug tracking has moved to a new Bugzilla host. This bug has been copied to https://bugs.gnucash.org/show_bug.cgi?id=795405. Please update any external references or bookmarks.