gnucash is confused by timezone changes

Bug #365065 reported by Martin Pool on 2009-04-22
14
This bug affects 3 people
Affects Status Importance Assigned to Milestone
GnuCash
Confirmed
High
gnucash (Debian)
Fix Released
Unknown
gnucash (Ubuntu)
Medium
Unassigned

Bug Description

Binary package hint: gnucash

When I move my laptop eg from Sydney to London timezone, gnucash behaves poorly.

The user model is that transactions are done on a particular day; for example I pay my rent on the first day of each month. When creating a transaction in the ledger or through a recurring transaction, one can only choose a date for it to occur, not a time, and I doubt that tracking the time would be very useful.

However, it looks like gnucash internally stores the transaction as a timestamp, and displays them in the local time. So after moving to London, the rent payments appear to have been done on the last day of the previous month, and if I enter transactions in London they'll appear against the wrong day when I return home.

As a workaround you can set TZ before running gnucash.

There's some discussion of it here:

http://<email address hidden>/msg03494.html

On Sat, Nov 02, 2002 at 09:24:44AM -0600, John Goerzen wrote:
> Package: gnucash
> Version: 1.6.8-3
> Severity: important
>
> Recently I moved from one US state to another, and as a result, my timezone
> changed to one hour earlier.
>
> After making this adjustment on my computer, every single date in every
> gnucash register was made one day earlier. For instance, November 8 became
> November 7.

Upstream reports that dates are stored as being 00:00:01 in LOCAL time
which is obviously causing your problem. A bug is being filed upstream.

--
James (Jay) Treacy
<email address hidden>

Tags: upstream

Believing you, I tag this to help the maintainer.

tag 167453 upstream
thanks

Hi,

I can't find this bug upstream (I'd like to, so I can mark this bug as
forwarded and/or query the gnucash maintainers about it). Can anyone
find it?

Joachim
--
Joachim "nomeata" Breitner
  e-Mail: <email address hidden> | Homepage: http://www.joachim-breitner.de
  JID: <email address hidden> | GPG-Keyid: 4743206C | ICQ#: 74513189
  Geekcode: GCS/IT/S d-- s++:- a--- C++ UL+++ P+++ !E W+++ N-- !W O? M?>+ V?
            PS++ PE PGP++ t? 5? X- R+ tv- b++ DI+ D+ G e+>* h! z?
Bitte senden Sie mir keine Word- oder PowerPoint-Anhänge.
Siehe http://www.fsf.org/philosophy/no-word-attachments.de.html

On Sat, Nov 02, 2002 at 09:24:44AM -0600, John Goerzen wrote:
>
> Recently I moved from one US state to another, and as a result, my timezone
> changed to one hour earlier.
>
> After making this adjustment on my computer, every single date in every
> gnucash register was made one day earlier. For instance, November 8 became
> November 7.

This is really a problem with the data you imported as it did not
specify the timezone of the transaction (or gave erroneous data).
Older versions of ofx followed the spec and assumed GMT whenever there
was ambiguity. Under some circumstances this led to the problem you
have.

A long discussion upstream resulted in a compromise that follows
the spec as closely as possible while minimizing the chance of your
problem happening (IIRC, you'd have to import a bad file and then
move to one of the little used timezones in the Pacific).

You should contact the institution that is generating data that doesn't
follow the OFX spec and get them to fix the it.

As far as correcting the problem for your existing transactions,
upstream suggested only one fix: change the date for all affected
transactions to the correct date. Any future changes to your timezone
should not affect the date of the transaction.

I consider this bug closed. Please test the above solution and tell
me how it went. I won't close the bug until you respond (I'll give a few
weeks). In the meantime the severity of the bug will be downgraded to
normal.

--
James (Jay) Treacy
<email address hidden>

severity 167453 normal
stop

--
James (Jay) Treacy
<email address hidden>

On Wed, Oct 15, 2003 at 11:21:29PM -0400, James A. Treacy wrote:
> This is really a problem with the data you imported as it did not
> specify the timezone of the transaction (or gave erroneous data).

None of the data was imported; it was all keyed into Gnucash manually. That
seems to unfortunately render the rest of your message irrelevant; is that
the case, or am I misunderstanding something?

On Wed, Oct 15, 2003 at 10:36:43PM -0500, John Goerzen wrote:
> On Wed, Oct 15, 2003 at 11:21:29PM -0400, James A. Treacy wrote:
> > This is really a problem with the data you imported as it did not
> > specify the timezone of the transaction (or gave erroneous data).
>
> None of the data was imported; it was all keyed into Gnucash manually. That
> seems to unfortunately render the rest of your message irrelevant; is that
> the case, or am I misunderstanding something?

Life is never easy.

Can you check to see that this still occurs with 1.8.7?

--
James (Jay) Treacy
<email address hidden>

On Thu, Oct 16, 2003 at 12:40:47AM -0400, James A. Treacy wrote:
> On Wed, Oct 15, 2003 at 10:36:43PM -0500, John Goerzen wrote:
> > None of the data was imported; it was all keyed into Gnucash manually. That
> > seems to unfortunately render the rest of your message irrelevant; is that
> > the case, or am I misunderstanding something?
>
> Life is never easy.
>
> Can you check to see that this still occurs with 1.8.7?

Yes, it does. My timezone is US/Central. I ran TZ=US/Pacific gnucash &.
Sure enough, all dates went one day earlier.

-- John

This happens as the date is recorded as date and time 00:00 local time.
When the time zone changes west, the local shifts backwards and change of
date occurs. As the user has no notion of time, but just date this doesn't
make sense. I can see two way to resolve this: (1) don't change time zones
-- define a time zone per file or account; (2) show and allow for change of
time as well as date, default of 12:00. The default time of 12:00 will at
least allow 12 hours time zone change except for crossing the date line.
The time will show the actual time transactions happened, which may be
different date in a different time zone, but is fine now.

Both (1) and (2) are a possibility as well.

Bug 89439 requests an enhancement of option (2) above.

tags 167453 + confirmed
tags 167453 + upstream
thanks

severity 167453 important
thanks

reopen 167453
reopen 247831
tags 167453 + sarge,woody
tags 247831 + sarge,woody
thanks

tags 167453 - sarge,woody
tags 247831 - sarge,woody
reopen 120933
repoen 190118
tags 120933 + sarge,woody
tags 190118 + sarge,woody
thanks

*** Bug 150749 has been marked as a duplicate of this bug. ***

Raising Severity because of repeated reporting.

#
# bts-link upstream status pull for source package gnucash
# see http://lists.debian.org/debian-devel-announce/2006/05/msg00001.html
#

user <email address hidden>

# remote status report for #167453
# * http://bugzilla.gnome.org/show_bug.cgi?id=137017
# * remote status changed: (?) -> NEW
forwarded 167453 http://bugzilla.gnome.org/show_bug.cgi?id=137017
usertags 167453 + status-NEW

# remote status report for #248406
# * http://bugzilla.gnome.org/show_bug.cgi?id=143720
# * remote status changed: (?) -> RESOLVED
# * remote resolution changed: (?) -> RESOLVED
# * closed upstream
forwarded 248406 http://bugzilla.gnome.org/show_bug.cgi?id=143720
tags 248406 + fixed-upstream
usertags 248406 + status-RESOLVED resolution-FIXED

thanks

Issue still exists in 2.0.x/SVN.

*** Bug 420217 has been marked as a duplicate of this bug. ***

I share a gnucash file via SVN with someone who is one timezone away from me. We discovered this today when she opened the legdger and the date on numerous transactions were different from the ones I have in mine. They are off by one day, but that is really confusing because the file is exactly the same between us. She is using an older version (1.8.12) and I am at 2.0.5.

I dont think Gnucash should try and be smart about things like this, the dates entered should be the dates entered, not auto-corrected. Or at least have it as an option.

Is there any way around this? I tried to set my TZ to her locale before starting the application, but that didn't change it.

*** Bug 454943 has been marked as a duplicate of this bug. ***

# Automatically generated email from bts, devscripts version 2.10.8
found 167453 2.2.1-1

I just traveled to the UK from the USA west coast, my transactions are all a skew... graphs are a mess, cash flow is a mess, the list goes on and on.

Further (before realizing), I downloaded my recent transactions from my bank, I fear they will need manual adjustment when I return to my home time zone.

This is a *VERY* serious bug... first reported in 2004? Wow.... surly some gnucash developer has time to look into this?

...using GnuCash 2.2.1

I'm flattered. Why does gnucash not save the time/date as UTC internally?

Created attachment 115950
Test case for UTC-7

This is a file of 24 transactions, with a "posted" timestamp for each hour of the day. It can be used to observe or test this bug. This file was created in the UTC-7 time zone, but can easily be modified to be any other time zone.

Created attachment 115989
Proposed patch (first try)

The algorithm behind this patch is explained here:

https://lists.gnucash.org/pipermail/gnucash-devel/2008-August/023694.html

Created attachment 116610
Proposed patch (second try)

This is exactly the same as the first patch, except that warning messages are only generated for bug-affected timestamps.

Re comment 13, @Rolf: Your question concerned the sql backend, not the file backend. That's a new discussion - you should move that to a new bug which deals with the SQL backend.

Re comment 12: I'd like to commit this patch. I think it is a reasonable compromise to fix the bug and yet doesn't loose too much of backward and forward compatiblity.

However, the discussion in August still had one issue open, https://lists.gnucash.org/pipermail/gnucash-devel/2008-August/023757.html , the recognition of book closing transactions. I didn't understand how Charles' patch would "screw up" if book closing transactions are present. As I understand it, we need to add a marker that indicates a transaction was automatically created. I've added such a marker in r17731, awaiting back-port.

Christian, thank you for your comment.

I understood http://thread.gmane.org/gmane.comp.gnome.apps.gnucash.devel/24443/focus=24445 that the data stored inside the XML file is already incorrect. I'm not sure what the date/time-stamp should look like, so I cannot comment, but only refer to Derek here.

Re comment 14, Thanks Christian, I see that the marker for book closings has been added in r17731. I still don't have a development environment set up on my new Mac (in fact I'm just figuring out how to install 2.2.8) so I can't yet make adjustments to the patch to detect the marker. However this would still not help book closing transactions created in the past, since they wouldn't have the marker (I think this is what Rolf meant in comment 15). I'm not sure what we can do about that.

I think gnucash saves two timestamps for each transaction, doesn't it. One is the date for the receipt, the other when the transaction was keyed in. While respecting timezone for the latter it makes absolutely *NO* sense for the former. At least as long as gnucash assumes the transaction taking place at midnight which is what happens currently. We don't enter an hour for tx date, why is gnucash trying to be smart about this when it doesn't really have the data accurate up to the hour???

The entered data is only accured up to the date, no need to mess with timezones.

Martin Pool (mbp) wrote :

Binary package hint: gnucash

When I move my laptop eg from Sydney to London timezone, gnucash behaves poorly.

The user model is that transactions are done on a particular day; for example I pay my rent on the first day of each month. When creating a transaction in the ledger or through a recurring transaction, one can only choose a date for it to occur, not a time, and I doubt that tracking the time would be very useful.

However, it looks like gnucash internally stores the transaction as a timestamp, and displays them in the local time. So after moving to London, the rent payments appear to have been done on the last day of the previous month, and if I enter transactions in London they'll appear against the wrong day when I return home.

As a workaround you can set TZ before running gnucash.

There's some discussion of it here:

http://<email address hidden>/msg03494.html

Changed in gnucash:
status: Unknown → Confirmed
Changed in gnucash (Debian):
status: Unknown → Confirmed
Rolf Leggewie (r0lf) on 2009-04-22
Changed in gnucash (Ubuntu):
importance: Undecided → Medium
status: New → Confirmed

(In reply to comment #17)
> respecting timezone for the latter it makes absolutely *NO* sense for the
> former.

"respecting timezone for the latter does make sense, that is not true for the former."

What should happen with this patch? I remember there was a discussion on gnucash-devel a while ago. This patch is marked "accepted-commit_now", but I don't remember the discussion coming to any real agreement or conclusion.

I'm not sure. If I recall correctly, the patch is perfectly fine for anyone who hasn't used the book closing feature. For those that have used that feature, or will, the patch isn't good enough. It doesn't check for the book closing indicator (which was added after the patch was written).

There is also the open question of what to do about book closings that took place before the indicator was added. There is no way to detect them automatically, so I guess the user would have to be involved. That sounds like it would involve a druid, which is a huge effort as far as I'm concerned. Otherwise we'd have to make the conversion completely optional (pop up a question if posting times other than 00:00 are found in the file), which this patch doesn't do either.

I would like to point out a possibility that may not have been considered in order to resolve this problem. I am not familiar with the code but I have some understanding of timezone issues so my input may be useful (or possibly not).

In the xml file the date posted for a transaction is stored as a local time with a UTC timezone offset. My timezone is UTC+0100 and if I enter a transaction for 20 July then the value in the xml file is '2009-07-20 00:00:00 +0100'. This is the start of 20th July (local time) with the fact that the local time is 1 hour ahead of UTC. In order to extract the date from this for display in the register it is merely necessary to interpret the date portion and ignore the rest. At the moment, I think, it is converted to the current local timezone first (which is what can cause the date change). It may therefore be that the problem can be fixed in this case merely by changing the interpretation of the data from the xml file. There is no need to change the format of the data in the file itself.

When the data is stored to a database however the situation changes. The date/time is stored in UTC with no indication of the timezone. Data is therefore lost (compared to the xml file) and I can see no way to recover it. I would therefore suggest that it may be highly desirable that this is sorted before the first full release of the version that supports the database is made available.

A possibility to avoid this is to store the posted date/time in local time rather than UTC. In the xml file this is just a matter of losing the +0100. In the database, normally entered transactions would have a time of 000000 (preceeded by the date) and special ones for book closing and so on can have 235959 or whatever is appropriate. As this would always be interpreted as a local time (whatever the user's current timezone) the date would always be exactly what is stored in the xml file or db.

Apologies if the above is a load of rubbish due to my lack of knowledge of the inner workings, but I thought I should provide my thoughts just in case they are helpful in finding a solution for this problem which seems to be rather problematic.

I'd like to see some action with this patch.

@Charles: (In reply to comment #20)
> It doesn't check for the book closing
> indicator (which was added after the patch was written).

Could you add support for that? gnucash 2.2.8 (December 2008) and later have that indicator, so at some point in time we can assume enough users will have it in their file.

> Otherwise we'd have to make the conversion completely optional (pop up a
> question if posting times other than 00:00 are found in the file), which this
> patch doesn't do either.

Could you add support for that as well? We should at least be able to fix this issue for users who didn't use the book closing, or used it only with 2.2.8 and higher. We can leave the implementation of a druid for later.

Once this two additional points are added, I would like to commit this patch to have it in 2.4.0.

Re comment#21: Please go through the full discussion here https://lists.gnucash.org/pipermail/gnucash-devel/2008-August/023757.html ; what you are proposing ("use the date part and ignore the rest") is basically saying we have a date data type in contrast to a date-and-time data type, which was also discussed there.

@Charles: (In reply to comment #20)
> It doesn't check for the book closing
> indicator (which was added after the patch was written).

Could you add support for checking the book closing indicator? gnucash 2.2.8 (December 2008) and later have that indicator. Since then, the 2009 book closing for sure has used this, so in order to get some improvement here, I vote for assuming all transactions of interest have this fixed.

In http://svn.gnucash.org/trac/changeset/18925 I've added a date setter for the transaction's posted-date which uses a GDate and stores it as a GDate kvp value. This works transparently for older existing transactions.

We can change the display and setting in the register to use a GDate as well, and eventually this will solve this issue correctly and for all cases.

Starting with http://svn.gnucash.org/trac/changeset/19555 the entered date is also saved as a kvp GDate, so that we at least have the date which the user saw when he entered it.

Changed in gnucash:
importance: Unknown → High

Im seeing this in 2.3.15. Disappointing to see this is in the latest dev yet logged in 2004. A transaction entered is specific to that entry no matter where I am, if I change timezone I'm not really seeing the logic that Id want the transaction times to change. This especially causes problems where someone else in another location is making the entries. Hope this gets picked up soon :)

*** Bug 643743 has been marked as a duplicate of this bug. ***

Download full text (7.6 KiB)

Charles Day wrote in the description of his patch at https://lists.gnucash.org/pipermail/gnucash-devel/2008-August/023694.html:

3. When reading a file, if the "HH:MM:SS" part is NOT equal to "00:00:00"
then the transaction is bug affected and must be reviewed to determine the
date the user originally entered in the register.
4. Once the date originally entered in the register has been determined,
GnuCash converts that date into a timestamp by imposing a default time of
day of midnight and using the LOCAL time zone.

I think this is a bad idea. It is based on repeated conversions between the following data types:

* the date data type (e.g. Julian date)
* the date-and-time data type (e.g. seconds since Epoch. Really this doesn't record "date" at all, just "time")

If I understand Charles's patch description correctly, his method is to use a date-and-time data type for in-file storage, convert it to a date data type upon reading the file, then converting this immediately back to a date-and-time data type for internal usage (not necessarily the same as originally in the file!). Finally, this adjusted date-and-time is written back to the file on saving. This is problematic in several situations:

* since the in-memory data structure is date-and-time, the display will be incorrect if the time zone changes after the file has already been loaded (e.g., when daylight savings time starts, or if one travels while GnuCashing. Given that I almost never quit GnuCash, I can sometimes have it open for weeks at a time).

* The adjustment in each direction is lossy. When converting a date-and-time to a date, one has to guess the timezone, and if the time zone changes by more than 12 hours, or across the date line, it is impossible to guess. Similarly, the conversion from date to date-and-time relies on the local time zone, which information is later lost.

* Since the algorithm continues to adjust the data each time the file is loaded, any errors that happen will accumulate over time. The data originally entered is lost. This is in contrast with a conversion to a new data type, which would only happen once.

I don't really understand the problem with price sources that Charles described, nor how the described method is supposed to fix it. By converting all times to midnight on reading, the time-of-day information is in any case destroyed, so GnuCash effectively has to do price lookups based on the calendar date. Finding a "nearest in time" price source with less than day granularity is out of the question if the actual time of day of the transaction is not known in the first place. So at best one can look up a daily price source (e.g. daily average, mid-day, closing, or some other fixed time of day).

Further, the price lookup should be independent of the local timezone of the up-looker. If I generate a report while travelling, I expect the report to be identical to what I would have gotten at home. If a user in Australia doesn't want to use London daily prices, then there should be a separate preference for that, such as "use Australian prices", or "use a price source for noon Australian time", or "use a price source for 1am GMT + 1 day", or "my company...

Read more...

It occurred to me today while writing a date-sensitive test function that we could solve most of the date problems by using GDates for most everything (GDateTimes might be appropriate for price quotes) and storing the GDate Julian value instead of a timespec. (The GDate Julian value is the number of days since 1 Jan 1, computed as if the Gregorian calendar had been in use since then.)

Is there a workaround for this other than requiring every computer that will be using GnuCash in an organization to stay on the same timezone forever? BTW, this is a critical bug. This is an accounting program, it is literally worse than useless if you can't depend on the transaction data to be preserved and displayed as entered. At least if it didn't work at all, you'd know to try something else, rather than entering months of data only to find that it has been manipulated into something that may or may not be correct.

(In reply to comment #30)
> Is there a workaround for this other than requiring every computer that will be
> using GnuCash in an organization to stay on the same timezone forever?

Yes, all you need is for all GnuCash processes in the organization to stay in the same timezone forever. When I first discovered this problem, I wrote a little wrapper script that runs "TZ=:America/New_York /usr/bin/gnucash". Now that I live in :America/Los_Angeles, I find it a little distracting that after 9 PM, the date defaults to tomorrow, but otherwise things are OK.

(In reply to comment #31)
> (In reply to comment #30)
> > Is there a workaround for this other than requiring every computer that will be
> > using GnuCash in an organization to stay on the same timezone forever?
>
> Yes, all you need is for all GnuCash processes in the organization to stay in
> the same timezone forever. When I first discovered this problem, I wrote a
> little wrapper script that runs "TZ=:America/New_York /usr/bin/gnucash".

How would that work for the Win32 version? I don't believe you can specify the TZ for specific processes in Windows. Is there a way to pass the TZ to gnucash as a startup parameter or manually enter it into a config file?

Searching for a solution to a problem I've recently encountered led me here to this Bugzilla entry. So, with much gratitude & appreciation for the work the GnuCash developers have already done in making such a great product, I'd like to `vote` for this bug to be fixed in an upcoming release.

My problem is that I share a GnuCash file between two computers; one is a Fedora Linux machine whose timezone is EST (Eastern Standard Time -- 5 hours behind UTC) and the other is a Windows XP machine whose timezone is EST5EDT. (I.e. it observes Eastern Daylight Time, so for most of the year it displays a time 4 hours behind UTC.)

The problem is that transactions during Daylight Saving Time entered on the Windows XP machine get stored like this:

  <trn:date-posted>
    <ts:date>2011-05-06 00:00:00 -0400</ts:date>

and thus when opened on the Fedora machine show a posted date 1 day prior since 2011-05-06 00:00:00 EDT is equal to 2011-05-05 23:00:00 EST.

It would be great if GnuCash was changed so that:

1. The assumed time of the transaction was noon instead of midnight; this would `hide` the problem from users of computers in timezones of less than 12 hours difference.

or

2. One could optionally enter a time and/or timezone in with a transaction's date. See https://bugzilla.gnome.org/show_bug.cgi?id=89439

or

3. One could set a `always assume timezone` option in the GnuCash file's preferences.

Thanks for considering these suggestions.

This is a serious problem for me, since my computer automatically adjustes the TimeZone according to my current location (this is actually useful). I believe the solution proposed by Peter Selinger is simple and pretty, see comment 28. I wonder why there is no discussion here. In any case, I hope this will be fixed as soon as possible.

*** Bug 707854 has been marked as a duplicate of this bug. ***

There is a date-posted kvp slot that is a gdate, since 2010.
https://github.com/Gnucash/gnucash/commit/59be2dc692513bdd71ecfe2de0fb2abaef069002
For a strange reason, I don't see it in all transactions, and I haven't identified a pattern: i.e. which transactions have it and which ones don't.

But if it was present in all transactions, then one could just modify the function that reads date-posted as an attribute to the transaction, and use the kvp slot instead.

I'm looking for a pattern, but if anyone else can pin down when it's written and when it's not, do add a comment here :)

Writing it is enabled in https://github.com/Gnucash/gnucash/commit/0871866c3882b99cadadd63c71b8a23220060a64. It will be called every time the transaction is entered manually, but won't be called for imports or SX.

It's also a bit of a hack and not really necessary. Date/times are stored as iso dates (e.g. 2014-07-03 08:03:20 Z), so we're totally free to replace the internal representation. Since date_posted is always just a date, we can just stop using timespecs (which I intend to do anyway as part of converting from gdate to boost::calendar) and treat date_posted as a date instead of a time.

(In reply to comment #37)
> Writing it is enabled in
> https://github.com/Gnucash/gnucash/commit/0871866c3882b99cadadd63c71b8a23220060a64.

Yes, that's r19555 mentioned in comment #25.

No, unfortunately it's not "not really necessary" currently - there is one difference. The point of this extra field is that the date-and-time field is ambiguous with respect to what date the user entered during entering this transaction. It is ambiguous because depending on the time zone the user's computer was set during entering (or viewing) the date-and-time, it could be one date or the other. Only the GDate field solves this ambiguity by stating which date the user really entered.

> Since date_posted is always just a date, we can just
> stop using timespecs (which I intend to do anyway as part of converting from
> gdate to boost::calendar) and treat date_posted as a date instead of a time.

Yes, I'm all for this change. As stated by various people including myself all along the years: The presentation shows only a date and not a date-and-time, so the storage should also do a date alone. Unfortunately the conversion from date-and-time to date is ambiguous because the conversion depends on the time zone for which it is done. This is exactly this bugreport here. Switching from a date-and-time type to a pure date type will also solve this bug.

This bug us still occuring in version 2.6.11 built 2016-01-11 on Windows 7 and 8.

I just realised that about 7 weeks of work, 100's of transactions, are now invalid. I was doing some catchup during some offtime in New Zealand.

Is there any way to fix the data file to at least another time-zone? Setting the timezone to New Zealand on the PC is not an option.

I see the last patch is almost 8 years old, and did not resolve the bug. Is there any fix being worked on and do we have an ETA for it?

The transactions aren't invalid, they're just off by a day. You can edit the date on all of them if it's actually important.

We've just been discussing this in the developer list. We've decided to change the posted date timestamp to 1100 UTC regardless of timezone for 2.6.14 (2.6.13 release is too close to be sure of getting that change done in time). The local adjustment from that will remain the same day everywhere except the middle of the Pacific. We'll include a scrub function that modifies old data. We'll also make the data-load code able to read date-only posted dates; writing those will be in 2.8.

Changed in gnucash:
status: Confirmed → In Progress

First, thanks for the prompt reply. Im glad the product is still being supported as I like the application.

(In reply to John Ralls from comment #40)
> The transactions aren't invalid, they're just off by a day.

Ok, invalid is a bit strong, but there are some dates that should be on the 1st day of them month that is now the last day if the previous month. This skews any form of monthly reporting, so yeah the transaction is off by a day which makes the report invalid.

>You can edit the date on all of them if it's actually important.

Is there a way to edit them all at once, or do I have to go to each one and increase the day manually? There are literally 100's of them, so to edit each individually, will be a huge task.

> We've just been discussing this in the developer list. We've decided to
> change the posted date timestamp to 1100 UTC regardless of timezone for
> 2.6.14 (2.6.13 release is too close to be sure of getting that change done
> in time). The local adjustment from that will remain the same day everywhere
> except the middle of the Pacific. We'll include a scrub function that
> modifies old data. We'll also make the data-load code able to read date-only
> posted dates; writing those will be in 2.8.

Understood, and glad to hear. What is the time-frame for 2.6.14?

Also, I noticed that this also affects scheduled transactions. I created a few scheduled transactions while I was in New Zealand, scheduled for the 24th of the month. They were created on the 23rd of the month for May and June.

There's no way to bulk-edit from the GUI, but you can try editing your data file. Figure out the timezone offset between your current TZ and New Zealand. Supposing that you're in the UK it would be 11 hours: British Summer Time is +1 and New Zealand Standard time is +12. All of the posted dates that you created in New Zealand will have a date-time that looks like 2016-0m-xx 13:00:00. You need to create an edit rule that changes that to 2016-0n-yy 00:00:00, where n and yy represent the day after m and xx.

If you're willing to change the timezone on the computer back to New Zealand long enough to open and re-save the file, it will be easier: In that case just search and replace +1200 to whatever is your offset and save. Change the timezone back and open it in GnuCash and everything should be OK.

Be sure to save the file as plain text, ideally in UTF-8. Make a backup first!

The GnuCash release schedule is http://wiki.gnucash.org/wiki/Release_Schedule.

Thanks for pointing out the scheduled transaction scheduled dates. Those should clearly get the same treatment.

I am so glad this bug will finally get fixed. Meanwhile, for what it is worth, since I travel a lot and this really used to mess up my accounts, I have used the following workaround for the last several years (this works in Linux): I defined an alias

alias gnucash="TZ=ADT+3 gnucash"

This way, the GnuCash process will always use my home timezone (which is ADT+3), regardless of what timezone the rest of my system is set to.

I've pushed phase 1, which is the change to 11:00 UTC for most places (it's adjusted in timezones -12, +13, and +14 so that it won't immediately jump a day on them), to maint. It will be in 2.6.14. It includes a scrub to move all of the old transactions' times. The scrub will use that GDate slot if it's available.

Still not the perfect fix of using a date only; that has to wait for 2.8.0.

(In reply to John Ralls from comment #42)
> If you're willing to change the timezone on the computer back to New Zealand
> long enough to open and re-save the file, it will be easier: In that case
> just search and replace +1200 to whatever is your offset and save. Change
> the timezone back and open it in GnuCash and everything should be OK.

I was in New Zealand again for a few weeks. Since I have come back I have not used GnuCash again, so the file is still at GMT+12. I had a look now, and saw a few things to watch out for.

Some of the transactions have the following tags:
  <trn:date-posted>
    <ts:date>2016-04-26 00:00:00 +1200</ts:date>
  </trn:date-posted>
  <trn:date-entered>
    <ts:date>2016-05-01 13:02:43 +1200</ts:date>
  </trn:date-entered>
  <trn:slots>
    <slot>
      <slot:key>date-posted</slot:key>
      <slot:value type="gdate">
        <gdate>2016-04-26</gdate>
      </slot:value>
    </slot>
  </trn:slots>

From the date entered I can see this was done in New Zealand, (and it has the +1200), so I can just change the +1200 to +0200 (Home is am at GMT+2)

However I also see these
  <trn:date-posted>
    <ts:date>2016-03-26 11:00:00 +1300</ts:date>
  </trn:date-posted>
  <trn:date-entered>
    <ts:date>2016-04-03 04:04:30 +1200</ts:date>
  </trn:date-entered>
  <trn:slots>
    <slot>
      <slot:key>date-posted</slot:key>
      <slot:value type="gdate">
        <gdate>2016-03-26</gdate>
      </slot:value>
    </slot>

and
  <trn:date-posted>
    <ts:date>2016-07-20 10:00:00 +1200</ts:date>
  </trn:date-posted>
  <trn:date-entered>
    <ts:date>2016-06-22 23:03:46 +1200</ts:date>
  </trn:date-entered>
  <trn:slots>
    <slot>
      <slot:key>date-posted</slot:key>
      <slot:value type="gdate">
        <gdate>2016-07-20</gdate>
      </slot:value>
    </slot>
  </trn:slots>

From the posted date, it looks like these was posted when I was still at home (00:00:00 +0200) and then updated by Gnucash to (11:00:00 +1300) and (10:00:00 +1200) when I was in New Zealand. The first was during daylight saving in New Zealand and the second normal time in New Zealand.

The gotcha here is that you cannot just replace (+1200) with (+0200). The search must also include the time, so search for (00:00:00 +1200). This may also be an edge condition to look out for in the scrub function.

I would like to point out another very simple solution for this issue. Set TIME value for date-posted to 12:00:00, instead of 00:00:00.

Thus changing time zone for +/- 11 hours will not change the DATE of transaction.

Best Regards,

(In reply to Litnitskiy Alexander from comment #46)
> I would like to point out another very simple solution for this issue. Set
> TIME value for date-posted to 12:00:00, instead of 00:00:00.
>
> Thus changing time zone for +/- 11 hours will not change the DATE of
> transaction.
>
> Best Regards,

See comment 44. 11:00 turned out to be a minute late as it still became 00:00 the following day in +13 (New Zealand Daylight Time). The code shifts more for +14 (Kiribati) and less of -12 (Wake Island).

Changed in gnucash:
status: In Progress → Expired

Based on this comment:

> We'll include a scrub function that modifies old data. We'll also make the data-load code able to read date-only posted dates; writing those will be in 2.8.

Is the scrub function automatic or something we need to run manually, and if manually, how?

And is there an option to change to writing date-only posted dates? I upgraded to 3.2 and it's not saving date-only and i can't find the option.

Thanks for this fix, and thanks in advance for information on using it!

I didn't follow through on the second part of comment 40, so GnuCash 3 still sets posted dateto 10:59Z.

Aha! So to be clear, this means that if GnuCash is regularly used by people in different timezones, all the posted times will change. (We're keeping our uncompressed .gnucash file in Git, so we notice and get scared by the big diffs, but it's good to know that with this fix they can't hurt anything.)

The time strings in the XML file will change, always representing the same time. That's actually always been true.

Changed in gnucash:
status: Expired → Confirmed

Is this bug still valid?

@Jean, I do not know the status of this bug in recent versions of Gnucash. However, there is a bug report https://bugs.gnucash.org/show_bug.cgi?id=797848 which is also about OFX handling of dates influenced by times and time zones. There is some discussion there about libOFX fixes which might be relevant to this bug also. I would add this as a See Also link, but for some reason I cannot edit that field for this bug.

Yes, I was just looking at the bug you linked!

Yeah, it's still valid. We have a pretty good work-around, see comments 40-44, but the ultimate fix is to use dates instead of times in places that times don't make sense, principally date posted. That will be a lot of work so I expect this bug to hang around for a few more years, in part because the work around has greatly reduced the pain level and therefore the priority.

Oh, it gets worse. splits.reconciled_date is a timestamp and you're setting it to "1970-01-01 00:00", which means that MariaDB refuses to accept it.

I worked around that by changing the column to datetime type for now.

Darn, I thought I'd changed all of the timestamps to datetimes for MySql/Maria. On top of that, of course, we shouldn't be using 0 to mean no value.

Please fix this, this had been designated a high severity, major bug for a reason. The fact that transaction dates can change without user intervention simply disqualifies this application from use by most organizations. And even if your accept this risk, the workarounds are only helpful to the minority of users who have the technical ability to implement them.

The only acceptable fix is to implement dates as dates, not datetimes. I don't know what else this affects, but someone has to do what is necessary to fix this or all the rest of the work that had gone into this is worthless because we can not use an accounting application with undependable transaction dates.

> The only acceptable fix is to implement dates as dates, not datetimes.

Then GnuCash isn't for you, at least for the next several years. Go find an accounting program that is.

John, your point about "GnuCash isn't for you" is well understood, but it is unfortunately also a very sad state of affairs. The transaction date bug is a major design flaw that has been in GnuCash from the very beginning, and that has been recognized as a major flaw since more than 16 years ago.

It is not in principle difficult to fix. I even started doing so myself in 2014. The reason I never finished fixing it is that the datatype used for transaction dates propagates to hundreds of places where it is hardcoded, crossing several programming languages and data representations (e.g., Scheme for the report system, XML for saved accounts, etc.). This makes it difficult to fix in an incremental way. I was simply not familiar enough with the sprawling code base to have any confidence that the bugfix wouldn't cause problems elsewhere - there are many subsystems of GnuCash that I never use at all. If there is any more experienced GnuCash developer who is actually familiar with how all these subsystems hang together, I'd be happy to have another go at fixing this bug.

Peter, not in principle, no. But it hasn't gotten any easier in practice. Well, maybe a little, because we've gotten rid of most of the separate Scheme date code and call the wrapped C functions for most date and time handling.

Managing stored data compatibility, whether in XML or SQL, will be a major part of any such change. GnuCash's sprawling and massively interdependent code base remains a major obstacle to fixing lots of basic design goofs and this one is no different. The subsystems don't hang together, more like they're tangled together.

You're pretty much stuck with me for a developer to work with, though others might chime in if you ask on gnucash-devel or IRC. Derek is still around and was involved the early design though he hasn't done any GnuCash coding for many years.

There are independent pieces that could be done first, for example reworking all of the standard interval code (Quarter, Month, Week) to use dates instead of times and storage backend changes to handle dates as well as times.

What were you planning to use for a date type? GLib's GDate? It's the only ready-made date implementation I know of that doesn't have a timezone problem, but we're trying to move away from GLib in GnuCash's core, preferring the C++ standard library. Unfortunately std::chrono is time-oriented and its date type is timezone-sensitive, as are boost::date_time and ICU's.

(In reply to John Ralls from comment #61)
>
> What were you planning to use for a date type? GLib's GDate? It's the only
> ready-made date implementation I know of that doesn't have a timezone
> problem, but we're trying to move away from GLib in GnuCash's core,
> preferring the C++ standard library. Unfortunately std::chrono is
> time-oriented and its date type is timezone-sensitive, as are
> boost::date_time and ICU's.

I'm not a C++ developer, but isn't this dealt with by the new date operators in std::chrono?

https://en.cppreference.com/w/cpp/chrono/year_month_day

This is discussed in detail in the "Opening Keynote Meeting C++ 2019 - Howard Hinnant - Design Rationale for the chrono Library" at https://youtube.com/watch?v=adSAN282YIw

Maybe. I haven't dug into the details enough to be sure that all of the computations (e.g. for computing a date range for a report) involving it would be independent of time zone.

The catch is that it's C++20. LLVM has had partial support since version 7 (2018, Xcode 10) but while gcc has had partial support in their development branch since about that time it hasn't yet made it into a release. Until it does and that release is in the oldest Linux distro we want to support (which will probably be Ubuntu 20.4 LTS for GnuCash 5.x) we can't use it.

For the record the authoritative references are http://www.open-std.org/jtc1/sc22/wg21/docs/papers/2018/p0355r7.html and https://isocpp.org/files/papers/N4860.pdf#chapter.27.

is that the Peter Selinger of trading accounts fame that commented above?

My inclination would have been to use GLib's GDate, and then worry about moving away from GLib as a separate step. I don't think dates are rocket science; I could probably implement a reasonable API for the Gregorian calendar in plain C fairly quickly. I'm not sure if anything is needed besides string-to-date, date-to-string, increment/decrement date by n days/weeks/months/years. Leap years need to be implemented correctly but that is probably the only complication. But the point is that once the code uses GDate, it would be relatively easy to find-and-replace that by some other appropriate implementation. But the initial conversion to GDate (or equivalent) is harder.

GDate would be an acceptable impemlentation choice. GnuCash already uses them in several places, especially in calendar controls.

Your implementation would also need intervals, comparisons, and conversion to/from time64 and GDate. Quarters (13 weeks) are another commonly used period in accounting. An integer representation like GDate's julian day would be convenient for passing around. Leap years are easy: is_leap = y % 4 == 0 && !(y % 100 == 0 && y % 400 != 0). Localized string I/O would be the major complication.

Changed in gnucash (Debian):
status: Confirmed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.