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.
