I also unzipped GNUCASH file to check the date format and values inside - it is the same timestamp as in Postgres
> > On Apr 30, 2020 at 19:44, <Finfort (mailto:finf...@gmail.com)> 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 > <finf...@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 transactions 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 <finf...@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 wher e 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, finf...@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 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? >>>>> >>>>> 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 >>>>> >>>>>> >>>>> >>> > > _______________________________________________ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user If you are using Nabble or Gmane, please see https://wiki.gnucash.org/wiki/Mailing_Lists for more information. ----- Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
