[GNC] Working with dates in Postgresql DB

[John Ralls]

> On Apr 28, 2020, at 3:43 PM, finfort at gmail.com wrote:
> 
> Hi,
> 
> I have found a strange thing trying to create my report with SQL queries from Postgresql Gnucash database.
> 
> The column post_date in transactions table stores values in timestamp format (YYYY-MM-DD hh:mm:ss).
> 
> If I compare the post date in the transaction inside Gnucash program and in Postgresql database, I see this:
> 
> Gnucash program            Postgresql DB
> 
> 31/12/2017                        2017-12-31 00:00:00
> 
> 31/12/2017                        2017-12-31 10:59:00
> 
> 01/01/2018                        2017-12-31 22:00:00
> 
> ---------------------------------------------------------------
> 
> 02/01/2018                        2018-01-01 22:00:00
> 
> 02/01/2018                        2018-01-02 10:59:00
> 
> --------------------------------------------------------------
> 
> 31/12/2018                        2018-12-30 22:00:00
> 
> 31/12/2018                        2018-12-31 10:59:00
> 
> So, the same dates in DB can be different dates in Gnucash program and vise versa.
> 
> There are only 00:00:00, 10:59:00, 21:00:00, 22:00:00 values of time.
> 
> Please explain how this mystics work and what is the reason to save dates like this...
> 
> I cannot make my reports without correct converting of dates.

All date-times in GnuCash are stored in UTC but displayed in local time. Transaction posted_dates and a few others are stored at 10:59:00 UTC to produce the same date across most timezones (there are 27 hours of timezones so it's not possible for all of them. If you live along the international date line then the time is adjusted so that it matches the date in your time zone, but it will still change date on you if you go to a timezone more than 23 hours away). Some other date-times are forced to midnight local. I suppose from what you've posted that you're in a +2 with 1 hour DST so that the recorded time for those midnight dates in 2200 UTC in winter and 2100 in summer.  I didn't think that it forced anything to midnight UTC though. Might you have entered data while visiting somewhere with a UTC (aka GMT) timezone? 

Regards,
John Ralls