10:59:00 UTC is a very clever fix, or "kludge" as some put it. So
just using the bash shell:
$ date +%F-%Z # Format Strings %F with %Z for time zone
2020-04-30-CDT
This, of course, depends on locale on whatever system you're using.
You could even try:
$ date +%F-TZ=%Z-Week=%U-Day=%u
2020-04-30-TZ=CDT-Week=17-Day=4
But, of course, that's from a simple command line, and in GnuCash
dates are recorded either by direct input, or from .qfx, .ofx, .qif
files. From .ofx files the field:
cat 202001JANb.ofx | grep DTPOSTED | less
Not sure what the various sql queries would do with this.
Gives a series list this:
<DTPOSTED>20191231000000
<DTPOSTED>20191231000000
Which must mean lots of institutions which allow .ofx imports probably
want to ignore the actual time, and just give a date. But apparently
the only time allowed in the specification is 12am 0 Minutes 0
Seconds, or 000000. So maybe grep, awk or sed to give something like:
<DTPOSTED>20191231105900
<DTPOSTED>20191231105900
Is there something like a <DTONLYPOSTED> ("Date only posted")?
Unix time (EPOCH) is indeed a strange beast, but Wikipedia does a
decent job explaining why. Apparently UTC itself changed in 1971 from
the earlier version from 1961 (leap seconds, 32 bit integers, 60 herz
clocks from power frequency, etc.). Getting rid of the time
altogether would do the trick.
I'm using the old .xml backend with a Windows VM with GnuCash 2.xx,
but I do have old mysql and postgres files from BSD and Mac. I'll try
and play with those. I'm not a developer, just a user, but who knows,
I've trimmed and munged time stamps for zfs snapshots and some of the
code may be similar. And, as usual, many thanks to all the GnuCash
developers for all your work and generosity of time and help.
Gordon
On Thu, Apr 30, 2020 at 3:26 PM John Ralls <jra...@ceridwen.us> wrote:
>
> We show no times at all. The smallest useful division of time in accounting
> is a day.
>
> Unfortunately someone decided a long time ago to use unix time to represent
> dates, probably because in 1998 that was what was readily available.
> Naturally you want to display in local time, otherwise you have the problem
> of "today" not being aligned with what you think it is. This design decision
> has caused a great deal of pain over the years because people refuse to stay
> put and so the meaning of "today" changes depending on where they are.
>
> A few years ago we worked out that 10:59:00 UTC has the same date in nearly
> all time zones and set the time on the post_date timestamps to that value.
> That fixes the changing-date problem for almost everyone, the exception being
> timezones -11, +14, and +15. We adjust the 10:59:00 if you happen to be in
> one of those timezones so that today will be what you think it is, but if you
> then travel across the date line to a timezone more than 23 hours different
> you'll see the dates on your transactions change.
>
> The right solution is to change the date representation to not use time so it
> doesn't care about time zone, but that's a major change and so far nobody's
> had the appetite to take it on.
>
> Regards,
> John Ralls
>
>
> > On Apr 30, 2020, at 1:06 PM, GWB <g...@2realms.com> wrote:
> >
> > Curious about this. So is the goal to show all times in UTC on
> > invoices along with local time? I tend to default most things to UTC
> > (including local time on computers) and just display in some local
> > time if necessary. If GMT is available as a time zone, I use that
> > (GMT is a timezone; UTC is not). Last time I checked, GMT does not
> > change for DST (but some locales might try to use GMT with DST), and
> > displays the same as UTC. Time zones like Tehran's also differ by 30
> > minutes, not 60, from adjacent time zones. I think DST is silly, but
> > a fourth of the planet does not, so their call in their countries.
> >
> > Cyprus *might* be a special case, but probably not. My assumption is
> > that the entire Island is in the same time zone, but I don't know what
> > you would get if you pinged a time server in Akrotiri or Dhekelia
> > (British Overseas Territories), which seems to be GMT +3 (during DST).
> > Moscow, Ankara, Sudan, Hungary, and quite a few others are not EEST,
> > but rather UTC +3/GMT +3, which results in the same time.
> >
> > Postgres does have a somewhat confusing way of handling timestamp
> > (timestamp without timezone) versus timestamptz (timestamp with time
> > zone):
> >
> > https://dba.stackexchange.com/questions/2796/how-do-i-get-the-current-unix-timestamp-from-postgresql
> >
> > That applies when doing conversions to and from unix time; not sure if
> > it affects the situation here.
> >
> > Gordon
> >
> > On Thu, Apr 30, 2020 at 2:19 PM John Ralls <jra...@ceridwen.us> wrote:
> >>
> >> 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.
