John, I have found lost 4 transactions, they are from invoices but not connected with invoices. Found using your SQL with left outer join and ordering by invoices.date_posted. They have Null in invoices columns. They have vendor names In transactions.description... Found! All of them are transactions with splits “Lot link”, “offset between documents: Bill... Credit note ....”!
> > On Apr 30, 2020 at 22:19, <John Ralls (mailto:jra...@ceridwen.us)> wrote: > > > > GnuCash stores all dates as UTC but displays them as local, applying the > timezone rules for the date, not for today. So in EEST 2020-02-12 22:00:00 > displays as 2020-02-13, 2020-06-12 21:00:00 displays as 2020-06-13, but > 2020-02-21 21:00:00 displays as 2020-02-21. Regards, John Ralls > On Apr > 30, 2020, at 11:42 AM, finf...@gmail.com wrote: > > It is not just adding > one day, it depends on the time. > > Looks like time 00:00:00 is the same > date, not next. > > From 21:00:00 is the next date in most cases, but I > did not check all transactions manually =) > > How the program converts > this wrong dates to the correct ones in its GUI? > > I believe I have > found a correct way to convert all the dates including wrong ones to correct > dates in Postgresql (pgAdmin 4): > > > date(t.post_date AT TIME ZONE > 'UTC' AT TIME ZONE 'EEST') AS DATE_AT_timezone_EEST > > EEST is a correct > zone in my case. CEST does not work. > > The transactions.post_date type > is timestamp without timezone: 2017-12-31 21:00:00 > > t.post_date AT > TIME ZONE 'EEST' AS timestamp_AT_timezone_EEST gives 2017-12-31 21:00:00+3 > > > date(t.post_date AT TIME ZONE 'UTC' AT TIME ZONE 'EEST') AS > DATE_AT_timezone_EEST gives 2018-01-01 > > Looks strange but works. > > > 2. > > There are only 3 transactions with 22:00:00 not connected with > invoices. > > There is only 1 transaction with 21:00:00 not connected > with invoices. > > Thinking how to find them... > > > > On > 30/04/2020 21:25, John Ralls wrote: >> Hmm, true. Should be always, since > you're in a time zone east of the prime meridian. So you also want to > increment the day on those. I think it would be safest to do it in two > queries, the first one being >> >> update transactions post_date = > post_date + interval '1 day' where post_date::TIME != '10:59:00'; >> >> > and the second to update the time as before. >> >> Regards, >> John > Ralls >> >> >>> On Apr 30, 2020, at 11:07 AM, Finfort > <finf...@gmail.com> wrote: >>> >>> If post_date is 2017-12-31 22:00 or > 23:00, it means (sometimes?) the real date is 2018-01-01. At least in cases > where I manually checked the invoices. >>> Setting all times to 10:59:00 > will give wrong dates in the program. >>> Now they are displayed correctly > in the program somehow... >>> >>> >>> >>>> On Apr 30, 2020 at 20:59, > <John Ralls> wrote: >>>> >>>> I don't think that's necessary. >>>> > >>>> To fix the wrong times just do an update query, something like >>>> > >>>> update transactions set post_date::TIME = 10:59:00 where > post_date::TIME != 10:59:00; >>>> >>>> I don't know Postgresql's > date-time functions well enough to know if that syntax works, you might have > to adjust it a bit. You might create a table with a DATETIME column and put a > couple of rows in it to test against while you tweak. Make sure that GnuCash > isn't connected to the database when you run that. >>>> >>>> Regards, > >>>> John Ralls >>>> >>>> >>>>> On Apr 30, 2020, at 10:45 AM, Finfort > <finf...@gmail.com> wrote: >>>>> 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> 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 <finf...@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 > <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 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, > 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.
