That's exactly what happened a few years ago to a guy from California who did
some updates while visiting New Zealand.
Regards,
John Ralls
> On May 1, 2020, at 3:16 PM, David H <hell...@gmail.com> wrote:
>
> Might be a problem if you were in Perth on April 11 entering txns and then
> back in California on April 10 entering further txns using today's date ?
> i.e. if you travelled to a timezone that was still the previous day ?
>
> Cheers David H.
>
>
> On Sat, 2 May 2020 at 04:35, D. via gnucash-user <gnucash-user@gnucash.org>
> wrote:
> I understand that a lot of debate and discussion and heartache had gone into
> this, so I really don't mean to be a pain, but it would seem to me that if I
> entered April 10, 2020 for a transaction, and GnuCash then stored 2020-04-10
> HH:MM:SS (where HH:MM:SS represents any arbitrary time), if GnuCash then
> ignored the time portion from that point forth, then I'd see April 10, 2020
> no matter how many times I crossed the date line. If I'm in California on
> April 10, or in Perth on April 11, I'm presumably going to pick "today's"
> date for my transaction. The two dates would be different, even if the
> transactions were entered at the exact same time, but I could only see this
> as a potential problem for a business with offices in both cities.
>
> As I said, I don't remember all the details, but I'm sure there were solid
> reasons for choosing to take transaction dates and store them in UTC, to be
> converted back to some arbitrary time zone at a later time. It makes my head
> hurt, though.
>
> David
>
>
> -------- Original Message --------
> From: John Ralls <jra...@ceridwen.us>
> Sent: Fri May 01 23:30:37 GMT+05:30 2020
> To: "D." <sunfis...@yahoo.com>
> Cc: finf...@gmail.com, "D. via gnucash-user" <gnucash-user@gnucash.org>
> Subject: Re: [GNC] Working with dates in Postgresql DB
>
> 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.
_______________________________________________
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.
