David,
You're not thinking it through: It's about 11:00 on Friday 1 May here in
California but it's 03:00 on Saturday 2 May in Western Australia. Chopping off
the time doesn't solve anything, a point illustrated by Finfort when he pointed
out that just changing the time on the errant dates would put them in the wrong
day.
Regards,
John Ralls
> On Apr 30, 2020, at 10:24 PM, D. <sunfis...@yahoo.com> wrote:
>
> Thanks for the reply. I do understand the challenges this poses-- both from
> the perspective of managing it on a daily basis, and from that of the
> difficulty of changing the underlying system. At least, conceptually!
>
> Is there any option to simply ignore the time portion in these timestamps? It
> would seem to me that one could focus on that, and simplify the process piece
> by piece. Of course, not being a programmer, I'm just a silly voice in the
> wilderness.
>
> David
>
>
> -------- Original Message --------
> From: John Ralls <jra...@ceridwen.us>
> Sent: Fri May 01 10:32:09 GMT+05:30 2020
> To: "D." <sunfis...@yahoo.com>
> Cc: "finf...@gmail.com" <finf...@gmail.com>, "D. via gnucash-user"
> <gnucash-user@gnucash.org>
> Subject: Re: [GNC] Working with dates in Postgresql DB
>
> David,
>
> I don't know why the decision was made to use time, it was taken long before
> I joined the project, but it probably has to do with that being the way
> computers keep time, in UTC and the accompanying date-time manipulation
> functions in the C standard library were readily available. Over the years
> various developers have piled more manipulation functions on top of it, or
> added other libraries to the side because they made doing something easier,
> and splattered it all over the code so that changing the base design would
> involve chasing down and reworking all of those disparate functions. As I
> said, no one has expressed much enthusiasm for taking it on.
>
> Knowing now that using time instead of date was a poor decision is just
> applying 20/20 hindsight to criticize Linas's decision 22 years ago. It can't
> change it. I can't say that I would have decided differently.
>
> Regards,
> John Ralls
>
>
>> On Apr 30, 2020, at 8:46 PM, D. <sunfis...@yahoo.com> wrote:
>>
>> John,
>>
>> I somewhat remember the discussion back in 2011 about the timestamp, but do
>> not recall all the details. Remind me why it is that GnuCash uses timestamps
>> in these date fields? All these steps and workarounds that take place to
>> present the proper date around the world.
>>
>> Wouldn't it be simpler just to store a bare date?
>>
>> Or just ignore the time portion and drop the conversion between UTC and
>> locale time altogether?
>>
>> Everyone refers to them as dates ("transaction date" "invoice date" "posting
>> date"). The software arbitrarily uses the same time for everything in a
>> futile attempt to properly display dates for all timezones (the solution in
>> this thread underscores that fact, insofar as you are recommending the user
>> to arbitrarily change all other times to the "standard").
>>
>> Of what use is it to store the added detail of an arbitrary timestamp in a
>> field that is treated everywhere as a date? What is gained?
>>
>> David T.
>>
>>
>> -------- Original Message --------
>> From: John Ralls <jra...@ceridwen.us>
>> Sent: Fri May 01 00:48:57 GMT+05:30 2020
>> To: "finf...@gmail.com" <finf...@gmail.com>
>> Cc: Gnucash Users <gnucash-user@gnucash.org>
>> Subject: Re: [GNC] Working with dates in Postgresql DB
>>
>> 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.
>>
>
>
_______________________________________________
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.
