sqlite, fx, and timezones

[Doug Brown]

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??



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
>
>
>