sqlite, fx, and timezones

[John Ralls]

On Oct 8, 2013, at 9:27 PM, Doug Brown <toquehead at gmail.com> wrote:
> 
> On Wed, Oct 9, 2013 at 2:00 PM, John Ralls <jralls at ceridwen.us> wrote:
> 
> On Oct 8, 2013, at 8:11 PM, Doug Brown <toquehead at gmail.com> wrote:
> 
> > I am writing some custom reports against a gnucash sqlite database and have
> > a problem with currency conversion.
> >
> > I have a transfer from a $CDN bank account to a $AUD bank account. My
> > computer is running with a TZ of Australian Eastern time, UTC+10:00.
> >
> > My Canadian bank records the date as 2013-08-06. My Australian bank records
> > the date as 2013-08-09. The transfer involved an intermediary, so the 3 day
> > delay is not unexpected. The register shows the transaction with a date of
> > 2013-08-09.
> >
> > I don't remember how this transaction was entered - it could have been
> > entered manually (seems unlikely), or imported from a Canadian sourced OFX,
> > or imported from an Australian sourced OFX.
> >
> > If I download the OFX with that transaction from my Canadian bank today, it
> > contains:
> > <DTSERVER>20131008230328[-5:EST]
> > <DTPOSTED>20130806020000[-5:EST]
> >
> > If I download the OFX with that transaction from my Australian bank today,
> > it contains:
> > <DTSERVER>20131037020637
> > <DTPOSTED>20130809
> > <DTUSER>20130809
> >
> > The record in the transaction table for this transaction has a post_date of
> > 20130809005900.
> >
> > The record in prices table for the currency exchange has a date of
> > 20130808140000.
> >
> > So the prices table's date is 10 hours and 59 minutes later.
> >
> > Why the discrepancy?
> >
> > How is it GnuCash can find the correct fx conversion when I can see nothing
> > in the database that stores TZ information?
> >
> 
> Would it help to know that in the SQL backend, all times are stored in UTC to save having to store
> a time zone?
> 
> Regards,
> John Ralls
> 
> 
> 
> Thanks John, that's handy to know, but I don't know how that can be in this case. The transaction date doesn't match the currency's price date; they should be the same, as far as I can figure.
> 
> Somehow gc still finds the correct exchange rate.
> 
> If I manually enter that transaction, no new price records are inserted. If I manually enter that transaction on the follow day, 2013-08-10 the price record is inserted with a date of 20130808140000. If I enter another transaction on the same day with a _different_ exchange rate, there are no new price records inserted. Where is the fx info stored in this case??
> 

Prices are recorded as plain dates (i.e., no times) which is converted to midnight local time, which for you is 1400 the day before UTC. Yes, that's not ideal, particularly for securities where one might get several prices a day. When you enter a transaction by hand, the posted date is also date-only and is treated the same way. I don't know whether the importers take a whole date-time if one is presented to them, but in the case of your Australian bank, one isn't, so it looks like the importer is setting it and making a Summer Time adjustment but no time zone adjustment, which isn't exactly correct -- but since we display only dates in the UI doesn't matter a whole lot until you set a different TZ in your computer: Then it makes a bit of a mess.

The exchange rate is the ratio between the amount (in C$) and value (in A$)  of the split to the Canadian account. The price table is used for computing the market value of assets, not the book value. Again, we're displaying by day, so once we have a price for a day we're happy and don't add another unless it's done explicitly in the Price Editor or with the --get-prices command-line switch.

Regards,
John Ralls