Bug 36858 - Oracle Report Builder date calculation error
Summary: Oracle Report Builder date calculation error
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
(earliest affected)
3.3.2 release
Hardware: x86 (IA32) Linux (All)
: medium critical
Assignee: Not Assigned
Whiteboard: target:4.1.0 target:4.0.3
Depends on: 63478
  Show dependency treegraph
Reported: 2011-05-04 17:36 UTC by mikko
Modified: 2013-08-16 18:46 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Date addesd right - Integer of date is added wrong. (16.46 KB, application/pdf)
2012-07-28 08:06 UTC, Robert Großkopf
Date shown formatted as date and formatted as number. (48.60 KB, application/vnd.sun.xml.base)
2012-08-28 15:12 UTC, Robert Großkopf

Note You need to log in before you can comment on or make changes to this bug.
Description mikko 2011-05-04 17:36:35 UTC
Report Builder 1.2.1 gets date(field type: date) from table and adds integer to it. Result is wrong.

datafield result    int
[date]    10.04.11  40643
[date]+1  09.04.11  40642
[date]+2  10.04.11  40643
[date]+3  11.04.11  40644

Date shows correctly, but calculated dates are wrong as shown above.
Expected results would be:

datafield result    int
[date]    10.04.11  40643
[date]+1  11.04.11  40644
[date]+2  12.04.11  40645
[date]+3  13.04.11  40646

Tested on Ubuntu 11.04 with LibO installed from LibO website debs because there are no toolbars in form designer of default Ubuntu LibO installation.
Comment 1 Alex Thurgood 2011-05-27 08:19:53 UTC
FWIW, I filed this report a while ago, but it seems difficult to confirm :


Bear in mind, that this was date calculation in general, and not specifically related to the ORB, but we see the same offset.

Additionally, and for those who know a bit about the history of the OpenOffice.org project, this problem keeps cropping up from time to time in the OOo source code, without anyone being able to precisely pinpoint why, i.e. whether it is a locale issue, and therefore NLS dependent, or whether it is some kind of conflict in the way date calculation functions are handled between Calc and Base.

Comment 2 Björn Michaelsen 2011-12-23 12:02:43 UTC
[This is an automated message.]
This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it
started right out as NEW without ever being explicitly confirmed. The bug is
changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back
to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases.
Details on how to test the 3.5.0 beta1 can be found at:

more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
Comment 3 Robert Großkopf 2012-07-28 08:06:58 UTC
Created attachment 64813 [details]
Date addesd right - Integer of date is added wrong.

I have tested with LO 3.3.4. When I added "2" to the date it gives the same date and the same Integer as in the database.
Then I tested in LO The date is now calculated right - but the integer is still the same problem.
The date of the database is linked with the wrong integer.
Comment 4 Robert Großkopf 2012-07-28 10:14:05 UTC
I have tried the following under LO 3.3.4 and LO
1. Create a query for a table with a Date-field.
2. Set one Date to "30.12.1899" (12/30/1899 - right in Englisch, USA?)
3. Change the format of the column to Standard-Number

In LO 3.3.4 it is shown as -2! (Also in OOo 3.3)
In LO it is shown as 0!

Could be the HSQLDB starts with 01.01.1900??
Comment 5 Rainer Bielefeld Retired 2012-08-28 05:38:42 UTC
I did some tests with and 3.5.2 and a very simple database only containing 2 records with only dates, and I was not able to reproduce problems, Date and integer always has been shown as expected. But I do not know how to do those date calculations, so my results currently are worthless.

Please attach your sample document
Comment 6 Robert Großkopf 2012-08-28 15:12:55 UTC
Created attachment 66234 [details]
Date shown formatted as date and formatted as number.

There is a different behaviour in LO 3.3.4 and LO rc. Adding of "2" to a date works in LO rc. But the internal value (integer-value) you could also see with the function DATEVALUE([Date]) is also wrong in LO rc. Starts with the right value, and two days later (by adding "2" to the formmated date) the integer-value is the same as at the beginning.
Comment 7 Jochen 2012-08-28 15:40:30 UTC
I can confirm the phenomen with LO 3.5.5

With LO 3.3.x all works right?
If yes: regression?
Comment 8 Robert Großkopf 2012-08-28 16:15:02 UTC

It won't work under LO 3.3.4. See the bug as reported: When you add "2" to a date-field, it shows the same date as the original. When you add "1" to a date-filed it's one day before. The integer-numbers of the field show the same behaviour. Note, that this is only another way of formatting a field.

Under LO rc it shows the right date. When you add "2" the new date, formatted as a date, shows a date two days later.
But the integer-formatted fields shows the same behaviour as before. Seems, as if somebody has added "2" in the internal function of the report-builder, wenn there appears a date formatted as date to fix this bug. The integer-numbers are the same as in LO 3.3.4. 

I don't know if anybody has tried a fix - looks better in LO rc, but isn't right. The problem is only hidden and not gone.

Set the status to New.
Comment 9 Alex Thurgood 2013-04-12 07:47:14 UTC
Hmm, OK, my bad, I should've put Lionel on CC.

@Lionel : care to take a look ? This has been a recurring problem for as long as I can remember with Base, where satellite bits of the underlying problem seem to get fixed, but the actual core cause remains unknown.

Comment 10 Alex Thurgood 2013-04-12 07:52:32 UTC
I realize that this might be a horrible can of worms with lots of potential "finger pointing" affected parts :-/

Comment 11 Alex Thurgood 2013-04-12 07:53:51 UTC
This also needs to be retested on a recent production or test version of LO to see if problem is still there.

Comment 12 Alex Thurgood 2013-04-12 07:56:02 UTC
Hmm, on the German users list, confirmed separately as still present in :

Von: *Ulrich Knoepfel*
Datum: 8. April 2013 20:49
Betreff: Linux- (und Mac) Version von Libreoffice/Openoffice Base verändern Datum

Libre Office und frühere

Openoffice 3.4.1 und frühere

Die Linux-Version von Libreoffice/Openoffice Base verändert gewisse Geburtsdaten, indem die Personen um 1 Tag oder mehr "älter" werden. 

Comment 13 Lionel Elie Mamane 2013-04-12 08:30:03 UTC
I don't understand the problem (in LibreOffice 3.6 and later). The bug log seems to say that now everything is "displayed correctly", but complains about "the integer value of the date". Well, there is no canonical way to convert a day to an integer, so how is whatever result comes out "wrong"? Could someone clearly show me an example of something that is wrong in LibreOffice 4, and (if not obvious) why it is wrong?
Comment 14 Robert Großkopf 2013-04-12 14:02:50 UTC
Have a look at the example-database (https://bugs.freedesktop.org/attachment.cgi?id=66234). When you open the report, there would appear 2 columns:
       Format: Date  Format: Number
Date   1899-12-30    0
Date+1 1899-12-31    -1
Date+2 1900-01-01    0
Date+3 1900-01-02    1

When the bug has been reported you could add to a field, which contains a date, the integer "2" and get the same date. If you added 0, the date has been set two days before the original date.
This has been corrected. But it has only be corrected by a workaround of the format. The internal values of the date are wrong. This do you see, when you have a look at the same date, only formated with the number-format "number", not "date". 
1899-12-30 has the internal value 0. This is the "start-date". 
1900-01-01 has the internal value 0. This is "start-date"+2. I expect, that this should have the internal value 2.
Comment 15 Commit Notification 2013-04-12 17:10:40 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":



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:
Affected users are encouraged to test the fix and report feedback.
Comment 16 Lionel Elie Mamane 2013-04-12 17:13:14 UTC
OK, the situation now is inconsistent, and this is indeed a bug. The "start date" (or epoch) should be the same in all situations.


1) I don't feel bound to make the epoch 30/12/1899,
   rather than 1/1/1900 or 13/9/1515 or any other date.
   Note that in Calc, the epoch is (per-file!) setting between three choices.

2) I don't feel bound to make the integer be 'days since the epoch'
   rather than 'seconds or hours or ... since the epoch'

The existing reportbuilder seems to have 1/1/1900 as epoch, so I'm unifying all cases in the example document to that. Maybe other corner cases are still wrong, but the true complete fix goes through fixing bug 63478 first, and then Report Builder stays out of date-to-integer conversion: if it gets a date, it puts a date, and formatting takes care of making a number of it, and vice-versa.
Comment 17 Alex Thurgood 2013-04-12 17:57:18 UTC
The call HSSFDateUtil.getExcelDate() can use either 01/01/1900 or 02/01/1904 if I understand the documentation correctly here :


StarOffice always used 30/12/1899 - and there must have been a reason for that choice, which we might never know, seeing as it probably goes back to somewhere in the late 1990's.

I don't have any preference other than consistency in handling across the whole of LO.

Comment 18 Alex Thurgood 2013-04-12 18:00:36 UTC
Note that this change, if I have understood its significance correctly, will also make Reports behave differently between LO and AOO/OOo/Symphony/NeoOffice.

Comment 19 Commit Notification 2013-04-15 07:27:26 UTC
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-4-0":



It will be available in LibreOffice 4.0.3.

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:
Affected users are encouraged to test the fix and report feedback.
Comment 20 Robert Großkopf 2013-08-16 18:46:11 UTC
Have forgotten to confirm, that this bug has been fixed.
Thank you, Lionel.