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.
