[GNC] Working with dates in Postgresql DB

Wed Apr 29 05:18:04 EDT 2020

Dear John,

Thank you  for your response.

I have collected some statistics from my DB.

My DB has 1724 records - transactions.

This is my SQL query, it is pretty simple and shows all the combinations 
of times in posted_date timestamps in transactions table, number of 
repetitions for that time value, min enter_date, max enter_date:

SELECT
     t.post_date::TIME as "POST TIME",
     COUNT(t.post_date::TIME) as "REPS",
     min(t.enter_date) as "MIN ENTER DATE",
     max(t.enter_date) as "MAX ENTER DATE"
FROM transactions t
GROUP BY t.post_date::TIME
ORDER BY t.post_date::TIME

Here are the results:

----

POST TIME   REPS      MIN ENTER DATE             MAX ENTER DATE

"00:00:00"    18        "2020-01-26 18:07:14"    "2020-01-28 19:11:07"
"10:59:00"    1177    "2019-12-23 17:55:29"    "2020-04-23 11:24:24"
"21:00:00"    251      "2020-01-08 17:43:54"    "2020-04-23 10:36:33"
"22:00:00"    256      "2020-01-08 17:06:59"    "2020-04-23 11:24:08"
"23:00:00"    22        "2020-01-27 19:16:04"    "2020-01-28 19:39:49"

----

I live in Cyprus, here is UTC +2 and summer time UTC +3, as I know.

I started to study Gnucash in December 2019 and have entered my data of 
2016-2020.

I never changed my place and time zone in the period of working with 
Gnucash.

1. Most of the records have time in date_posted 10:59:00 for all the 
period of data entering.

2. Only 2 days of entering have the results of 00:00:00 - 18 records.

3. Only 2 days of entering have the results of 23:00:00 - 22 records.

4. 21:00:00 and 22:00:00 - 500+ records - 30% of transactions for all 
the period of data entering.

Can you please explain that?

Why I have so many different time stamps? When and why the system 
decides to write time different from 10:59:00?

I understand that the system writes real ENTERING date and time and it 
is reasonable to use the time zone somehow.

When I POST the document with exact date in it I suppose to see this 
POST DATE the same wherever in Cyprus or UK or USA. But entering the 
same date I can have 5 different results. How it works and what is the 
reason - I have no idea...

Maybe you can give some examples and the algorithm to convert these 
dates? Where else I have to convert dates?

Regards,

Dimon.

On 29/04/2020 07:06, John Ralls wrote:

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