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.
