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.
