Date fields in gnucash sqlite database

[John Ralls]

> On Feb 4, 2017, at 7:57 AM, Keith <keith at kjbean.plus.com> wrote:
> 
> I am extracting data directly from the database using SQL queries. Although I have no problems with any other fields, I have some difficulties with dates. The date fields are just text strings and easy enough to manipulte apart from the fact that there the last six digits are a timestamp. In the post_date field, the timestamp is usually 230000 but is sometimes 000000. When it is 230000 the YYYYMMDD part of the field is one day early compared with the date shown on screen or in standard reports. When it is 000000 the YYYYMMDD portion is correct. I can't see any pattern justifying these variations. Can anyone explain them to me please?

It's because of daylight savings (a.k.a summer) time. When DST is in effect, timestamps from standard time are an hour earlier. There should be a timezone indicator in the date string indicating what correction you need to make.

We changed the way posted dates were recorded in 2.6.12: Before that version the timestamp was midnight local time; this caused dates to move around when GnuCash was used in different timezones (see https://bugzilla.gnome.org/show_bug.cgi?id=137017). From 2.6.12 the date is recorded at 10:59AM UTC unless you're in a timezone where that wouldn't be the current day (UTC-12 and UTC+14) in which case the timestamp is shifted as necessary to keep the date the same.

Regards,
John Ralls