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 dd.mm.yy 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.
FWIW, I filed this report a while ago, but it seems difficult to confirm : http://openoffice.org/bugzilla/show_bug.cgi?id=117214 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. Alex
[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: http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1 more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
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 3.6.0.2. 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.
I have tried the following under LO 3.3.4 and LO 3.6.0.2: 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 3.6.0.2 it is shown as 0! Could be the HSQLDB starts with 01.01.1900??
I did some tests with 3.6.1.2 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. @robert@familiegrosskopf.de Please attach your sample document
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 3.6.1.2 rc. Adding of "2" to a date works in LO 3.6.1.2 rc. But the internal value (integer-value) you could also see with the function DATEVALUE([Date]) is also wrong in LO 3.6.1.2 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.
I can confirm the phenomen with LO 3.5.5 @Robert With LO 3.3.x all works right? If yes: regression?
@jochen 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 3.6.1.2 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 3.6.1.2 rc, but isn't right. The problem is only hidden and not gone. Set the status to New.
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. Alex
I realize that this might be a horrible can of worms with lots of potential "finger pointing" affected parts :-/ Alex
This also needs to be retested on a recent production or test version of LO to see if problem is still there. Alex
Hmm, on the German users list, confirmed separately as still present in 4.0.2.2 : Von: *Ulrich Knoepfel* Datum: 8. April 2013 20:49 Betreff: Linux- (und Mac) Version von Libreoffice/Openoffice Base verändern Datum Libre Office 4.0.2.2 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. Alex
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?
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.
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=b8537fba16417aa5a2e940191f094b8671256ee5 fdo#36858 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.
OK, the situation now is inconsistent, and this is indeed a bug. The "start date" (or epoch) should be the same in all situations. But: 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.
The call HSSFDateUtil.getExcelDate() can use either 01/01/1900 or 02/01/1904 if I understand the documentation correctly here : http://svn.apache.org/repos/asf/poi/tags/REL_3_0_2_BETA1/src/testcases/org/apache/poi/hssf/usermodel/TestHSSFDateUtil.java 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. Alex
Note that this change, if I have understood its significance correctly, will also make Reports behave differently between LO and AOO/OOo/Symphony/NeoOffice. Alex
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "libreoffice-4-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=91021f11a30b1a2da4f878e9f245675ef6da17a1&h=libreoffice-4-0 fdo#36858 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: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Have forgotten to confirm, that this bug has been fixed. Thank you, Lionel.