[GNC] Working with dates in Postgresql DB

Thu Apr 30 13:45:21 EDT 2020

How can I help?

I can send you my gnucash file if it helps to find all the bugs.

And how can I change now my wrong dates in transactions?

>  
> On Apr 30, 2020 at 20:41,  <John Ralls (mailto:jralls at ceridwen.us)>  wrote:
>  
>  
>  
>  Yeah, it's definitely a bug. I easily found the wrong code and I'll fix it for 3.903 and 3.11. The query actually accounts for only 543 of the 547 wrong times, so there's another error somewhere else. Regards, John Ralls  >  On Apr 30, 2020, at 10:27 AM, Finfort  <finfort at gmail.com>  wrote:  >   >  Also I tried to unpost and post again. No changes.  >   >   >   >>  On Apr 30, 2020 at 19:44,  <Finfort>  wrote:  >>   >>  Hi John,  >>   >>  The result is:  >>   >>  22:00:00 253  >>   >>  00:00:00 18  >>   >>  21:00:00 250  >>   >>  23:00:00 22  >>   >>  So wrong dates are only when I use invoices.  >>   >>   >>   >>  On 29/04/2020 23:56, John Ralls wrote:  >>   >  Please remember to copy the list on all replies.  >>   >   >>   >  I take it that that means that you do in fact use the business invoice features. Let's see if that's the source of the problem. Run this query:  >>   >   >>   >  select t.post_date::TIME count(t.post_date::TIME) from transactions as t inner join invoices as 
i on i.post_txn = t.guid group by t.post_date::TIME;  >>   >   >>   >  Regards,  >>   >  John Ralls  >>   >   >>   >   >>   >>  On Apr 29, 2020, at 1:41 PM, Finfort  <finfort at gmail.com>  wrote:  >>   >>   >>   >>  But the program use business features like entering invoices or bills.  >>   >>  And we have this mess.  >>   >>  How we can manage that?  >>   >>   >>   >>   >>   >>   >>   >>>  On Apr 29, 2020 at 23:23,  <John Ralls>  wrote:  >>   >>>   >>   >>>  Dimon,  >>   >>>   >>   >>>  I'm in Silicon Valley, so 10 hours behind you.  >>   >>>   >>   >>>  By "simple invoices" do you mean that some of the transactions are created using business invoices?  >>   >>>   >>   >>>  My book goes back to 1993 and entry_dates begin in 2001. We changed the transaction time from local midnight in early 2011 so `select distinct time(post_date) from transactions;` returns  >>   >>>  10:59:00  >>   >>>  07:00:00  >>   >>>   >>   >>>  If I say instead `select distinct time(post_date) from transaction
s where post_date  >  '2011-01-01';` I just get 10:59:00.  >>   >>>   >>   >>>  But I don't use the business features, so if that's the problem my book won't show it.  >>   >>>   >>   >>>  Regards,  >>   >>>  John Ralls  >>   >>>   >>   >>>>  On Apr 29, 2020, at 10:13 AM, Finfort  <finfort at gmail.com>  wrote:  >>   >>>>   >>   >>>>  Hi John!  >>   >>>>  You are here finally!  >>   >>>>  Waiting for you all the day :)  >>   >>>>   >>   >>>>  All my data I have entered inside Gnucash 3.7, Ubuntu. No imports! Scheduled are ok!  >>   >>>>  Just simple invoices inside the program!  >>   >>>>  The SQL type conversions inside Postgres give better results with 22:00 but 21:00 show the same date again even in April - summer time where is for example 2018-06-04 21:00:00+03.  >>   >>>>  22:00+02 is 00:00 of the next day, 21:00+03 (summer time) is 00:00 of the next day but conversion does not work...  >>   >>>>  So, maybe you could try this SQL to check your records and revise the procedure which
 posts the data to DB?  >>   >>>>   >>   >>>>  Thank you,  >>   >>>>  Dimon.  >>   >>>>   >>   >>>>   >>   >>>>   >>   >>>>   >>   >>>>   >>   >>>>>  On Apr 29, 2020 at 19:50,  <John Ralls>  wrote:  >>   >>>>>   >>   >>>>>>  On Apr 29, 2020, at 2:18 AM, finfort at gmail.com wrote:  >>   >>>>>>   >>   >>>>>>  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 DAT
E"  >>   >>>>>>  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 h
ave 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?  >>   >>>>>   >>   >>>>>  As those are all posted dates you've found a bug or two as posted date should always have a 10:59:00 timestamp. The 21:00 and 22:00 times are clearly midnight local, and which one is used *should* be determined by whether DST is in effect for the posted date in your locale. It seems that 40 transactions somehow used UK time instead of Cypress time.  >>   >>>>>   >>   >>>>>  Did you enter all of the transactions from the GnuCash UI or did you import some of them? If you imported some is there any way to tell which were imported (and from where and by what method), perhaps by the accounts their splits are in or because you still have some of the import files?  >>   >>>>>   >>   >>>>>  Were any of them created by scheduled transactions?  >>   >>
>>>   >>   >>>>>  Regards,  >>   >>>>>  John Ralls  >>   >>>>>   >>   >>>>>   >>   >>>   >>  
>
>  