sqlite, fx, and timezones

[John Ralls]

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