Download it now!
Bug 63230 - Calc: Date decreased by 1 depending on Timezone
Summary: Calc: Date decreased by 1 depending on Timezone
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.5.7.2 release
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:4.5.0
Keywords:
: 79663 82805 (view as bug list)
Depends on:
Blocks:
 
Reported: 2013-04-07 13:00 UTC by Helmut Leininger
Modified: 2015-05-05 14:34 UTC (History)
10 users (show)

See Also:
Crash report or crash signature:


Attachments
Decreased dates and timezones. (21.06 KB, text/csv)
2013-05-09 13:11 UTC, Isamu Mogi
Details
Script which looks for similar bug (2.29 KB, text/x-python)
2013-05-09 13:14 UTC, Isamu Mogi
Details
Script to look for similar bug. (4.95 KB, text/x-python)
2014-05-22 12:33 UTC, Isamu Mogi
Details
Script to look for similar bug. (7.08 KB, text/x-python)
2014-05-31 02:13 UTC, Isamu Mogi
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Helmut Leininger 2013-04-07 13:00:05 UTC
This seems to be a relict of Bug https://www.libreoffice.org/bugzilla/show_bug.cgi?id=44286

with TZ "(UTC+01:00) Amsterdam, Berlin, Bern, Rom, Stockholm, Wien" if "1.4.1893" (April 1st, 1893) is entered, it is converted to 31.3.1893.

This does not happen with TZ "(UTC+01:00) Belgrad, Bratislava (Pressburg), Budapest, Ljubljana, Prag"
Comment 1 Thomas 2013-04-07 15:12:22 UTC
i do have the same problem.
using LO Calc 4.0.2.2 and Win XP SP3
Comment 2 Jorendc 2013-04-07 21:53:19 UTC
@Eike: seems very related to Bug 44286. Is your patch (http://cgit.freedesktop.org/libreoffice/core/commit/?id=6013fe19a40dd16ce435a2428f7405b51930689e) also applicable for timezones outside Brazil etc (as mentioned in fdo#44286)? Regression?

I mark this bug as NEW for the moment, because Thomas (Comment 1) can verify this behavior.

Kind regards,
Joren
Comment 3 Eike Rathke 2013-04-08 14:37:29 UTC
Sigh.. this seems to be yet something different, reproducible on Linux with TZ=Europe/Berlin also in master and for 3.6.6.rc2 and 3.5.7

Strangely enough it happens _only_ for 1893-04-01, not 1893-03-31 and not 1893-04-02, I assume there's a gap in the timezone on that date such that it was adjusted by some minutes and indeed the workaround is to enter at least

1893-04-01 00:06:32

to get that date. Looks like technically times between 1893-04-01 00:00:00 and 00:06:32 do not exist. April Fools' joke ;-)
Comment 4 Thomas 2013-04-08 17:55:39 UTC
On 1893-04-01 the time was corrected in that timezone. Before that date there was no official timezone in country. There is an articel on wikipedia (but its in german):
http://de.wikipedia.org/wiki/Gesetz_betreffend_die_Einf%C3%BChrung_einer_einheitlichen_Zeitbestimmung
Comment 5 Isamu Mogi 2013-05-09 13:11:43 UTC
Created attachment 79051 [details]
Decreased dates and timezones.
Comment 6 Isamu Mogi 2013-05-09 13:14:40 UTC
Created attachment 79052 [details]
Script which looks for similar bug
Comment 7 Isamu Mogi 2013-05-09 13:18:43 UTC
On Ubuntu Linux 13.04 64bit with LibreOffice 4.0.2.2,
I also confirmed that this bug affects about 300 timezones.

For example:
+ ---------------------------------+------------+-------------+
| TZ environment variable          | Input date | Output date |
+ ---------------------------------+------------+-------------+
| Africa/Accra                     | 1900-01-01 | 1899-12-31  |
| Africa/Addis_Ababa               | 1870-01-01 | 1869-12-31  |
| Africa/Algiers                   | 1956-01-29 | 1956-01-28  |
...
| America/Antigua                  | 1912/3/2   | 1912/3/1    |
| America/Argentina/Buenos_Aires   | 1894-10-31 | 1894-10-30  |
| America/Argentina/ComodRivadavia | 1991/10/20 | 1991/10/19  |
...
| Antarctica/Casey                 | 1969/1/1   | 1968/12/31  |
| Antarctica/Davis                 | 1957/1/13  | 1957/1/12   |
| Antarctica/DumontDUrville        | 1947/1/1   | 1946/12/31  |
...
| Asia/Almaty                      | 1930/6/21  | 1930/6/20   |
| Asia/Anadyr                      | 2011/3/27  | 2011/3/26   |
| Asia/Aqtau                       | 1924/5/2   | 1924/5/1    |
...
| Australia/Adelaide               | 1899-05-01 | 1899-04-30  |
| Australia/Broken_Hill            | 1895-02-01 | 1895-01-31  |
| Australia/Currie                 | 1895-09-01 | 1895-08-31  |
...
| Europe/Amsterdam                 | 1937/7/1   | 1937/6/30   |
| Europe/Andorra                   | 1946/9/30  | 1946/9/29   |
| Europe/Athens                    | 1916/7/28  | 1916/7/27   |
... and more than 500 lines.

Full result and scripts were uploaded as an attachment.

According to IANA Time Zone Database (http://www.iana.org/time-zones),
1900-01-01 of Africa/Accra has no special rule. But 1870-01-01 of
Africa/Addis_Ababa has a change of timezone format. So it may be very 
difficult problem :(
Comment 8 Isamu Mogi 2014-05-22 12:33:07 UTC
Created attachment 99582 [details]
Script to look for similar bug.
Comment 9 Isamu Mogi 2014-05-31 02:13:29 UTC
Created attachment 100184 [details]
Script to look for similar bug.

Following patch fixes this bug:
https://gerrit.libreoffice.org/gitweb?p=core.git;a=commitdiff;h=5b8c694b0df58c5258e63d247a247a944de57de3

However it may cause regression because it deletes bug fix code for fdo#44286 fdo#52619 #i24082# and more. I'll write regression test before publishing this patch to gerrit review.
Comment 10 Eike Rathke 2014-06-03 08:38:12 UTC
That patch would most certainly break all sort of things left and right..
Comment 11 Isamu Mogi 2014-06-14 10:30:13 UTC
The patch for this bug is updated: https://gerrit.libreoffice.org/gitweb?p=core.git;a=commitdiff;h=d4f46ca74998c49745109a95fce4bff6557d1bc6

Nevertheless I'll submit regression tests to gerrit for commits which is 
deleted by my patch. Because I think these are useful for fixing this bug
even if my patch will be rejected. I published
https://gerrit.libreoffice.org/#/c/9781 as one of those tests.

Of course I beleave strongly that my patch works fine.
Comment 12 Commit Notification 2014-06-18 09:49:56 UTC
Isamu Mogi committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=65b5d1a3f045faca462e7370a315cc729105df86

related fdo#63230: Add unit test for fdo#44286



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 13 Commit Notification 2014-06-19 06:25:55 UTC
Isamu Mogi committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=d2c469cc16bf303d4f5065a438d5252a31b3a564

related fdo#63230: Fix unit test for fdo#44286 to run in all locales



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 14 Eike Rathke 2014-07-09 11:37:06 UTC
(In reply to comment #11)
> Of course I beleave strongly that my patch works fine.

You need to test the actual cases that the code covers which you removed in your patch. That is not only the various TZ changes mentioned in related bugs but also the DST/non-DST border crossings as described in the comments. Also not only the preview string of the number formatter needs to be tested but also whether parsing an input of such date results in the expected serial number.
Comment 15 Eike Rathke 2014-08-23 16:21:51 UTC
*** Bug 82805 has been marked as a duplicate of this bug. ***
Comment 16 Eike Rathke 2014-08-25 11:14:04 UTC
So I took a deeper look at the patch mentioned in comment 11. It looks as if that would be a viable solution in principle, but as is by adding methods to an existing service it breaks API compatibility. Btw, NEVER edit and change offapi/type_reference/offapi.idl to make things build. If the checks fail it is for good reasons. I'd also not remove the existing code that resubmits values with adapted zone/dst offsets for all the known pitfalls at this point, but rather introduce a check of the newly submitted value against a retrieved value and maybe continue with the old code if it doesn't match.

I'll take care of this.
Comment 17 Alex Thurgood 2015-01-03 17:38:41 UTC
Adding self to CC if not already on
Comment 19 Eike Rathke 2015-02-19 16:02:46 UTC
*** Bug 79663 has been marked as a duplicate of this bug. ***
Comment 20 Eike Rathke 2015-02-19 16:05:52 UTC
*** Bug 74869 has been marked as a duplicate of this bug. ***
Comment 21 Joel Madero 2015-02-22 18:19:00 UTC
Please don't add bugs to the MAB list that are already fixed in master. The appropriate thing to do is to request Eike to backport it here on the bug - not to spam 50 people on the MAB list just to try to push for a backport. Thanks
Comment 22 Naruhiko Ogasawara 2015-02-23 22:47:38 UTC
@Eike,

Please backport your patch in comment #18 to 4.4, because this bug is annoying for people using TZ hitting it and I heard some users have to stay 4.2 because of it.


@Joel,

Thanks for your comment.
Comment 23 Eike Rathke 2015-02-24 13:16:48 UTC
I doubt the situation is better with 4.2, note the problem was reported also on 4.0.2 and probably stems back to OOo times. The fix is quite invasive, including a new API service. I'd like to be sure it actually works and does not introduce new problems before backporting it.

So, could you please build and test current master on *different* platforms, i.e. Linux, MacOSX and Windows, on Linux and MacOSX in builds against the system ICU and possibly different versions in different distribution releases. That would be very helpful.
Comment 24 Naruhiko Ogasawara 2015-05-05 14:34:02 UTC
@Eike,

Sorry for late response.
We, Japanese users, had discussed about this issue, and we can wait to LibO 5.0, I mean, backporting to 4.4 is not needed.