Timezones for dates with MySQL storage

[Andrew Wasielewski]

Please disregard as I have just worked it out...

The posting date is held in "gdate_val" in the "slots" table.  Select where slots.obj_guid = transactions.guid, and slots.name='date-posted'.

Simples...sorry for being such a doofus. 

Andrew  :$

On Thursday 25 July 2013 00:04:18 Andrew Wasielewski wrote:
> I am running GnuCash 2.4.13 with MySQL storage.  I now need to develop some custom reports using SQL.
> 
> I can get the transaction date from "post_date" in the "transactions" table.  Although GnuCash doesn't appear to make use of the time, it is stored as a datetime in MySQL.
> 
> I am confused by the (apparent) use of timezones.  I am in UK; it is currently Summer Time (UTC + 1), and all my transactions have been entered in the last couple of weeks (i.e. since Summer Time started).
> 
> Sometimes the post_date is midnight at the start of the posting date; other times it is 23:00 on the day before (i.e. different by the current TZ offset from UTC).  This occurs with scheduled transactions, and transactions generated from the *same* schedule display both behaviours.  Other times the time appears to be the actual entry time, but on the correct posting date.
> 
> GnuCash obviously "knows" what the posting date is, as it always shows the correct date.  My question is:- what SQL logic can I apply to consistently get the correct posting date?  
> 
> As a follow up, am I safe to assume that GnuCash won't have any problem when Summer Time ends and local time reverts to UTC?  I am most unlikely to need to change the system TZ toanything other than UK...
> 
> 
> Thanks for any advice,
> Andrew