[GNC] Working with dates in Postgresql DB
David H
hellvee at gmail.com
Fri May 1 18:16:28 EDT 2020
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 at 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 <jralls at ceridwen.us>
> Sent: Fri May 01 23:30:37 GMT+05:30 2020
> To: "D." <sunfish62 at yahoo.com>
> Cc: finfort at gmail.com, "D. via gnucash-user" <gnucash-user at 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. <sunfish62 at 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 <jralls at ceridwen.us>
> > Sent: Fri May 01 10:32:09 GMT+05:30 2020
> > To: "D." <sunfish62 at yahoo.com>
> > Cc: "finfort at gmail.com" <finfort at gmail.com>, "D. via gnucash-user" <
> gnucash-user at 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. <sunfish62 at 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 <jralls at ceridwen.us>
> >> Sent: Fri May 01 00:48:57 GMT+05:30 2020
> >> To: "finfort at gmail.com" <finfort at gmail.com>
> >> Cc: Gnucash Users <gnucash-user at 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, finfort at 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 <finfort at 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 <finfort at 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 <finfort at 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 <finfort at 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 <finfort at 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, finfort at 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 at 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 at 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.
>
More information about the gnucash-user
mailing list