[Libreoffice-bugs] [Bug 127476] Rounding behaviour of YEAR and SECOND function is inconsistent
https://bugs.documentfoundation.org/show_bug.cgi?id=127476 --- Comment #11 from b. --- sorry, i'm not convinced ... there are two to three overall targets: - excel conmpatibility, - obeying standards, just installed ex$el 2010 winx64 for a countercheck, results: year 2000, second 0, (month 1, day 1, hour 0, minute 0), calc: year 1999, second 0, (month 12, day 31, hour 23, minute 59), if calcs results are required by a standard ... - change that standard, or - produce results which are not! 59,9 seconds off, (as @Eike said: 'Yes rounding SECOND() is odd, complains go to Excel', but then either avoid ex$el behaviour, or copy it in full, not copy the odd thing reg. 'compatibility' and leave out the corrections necessary to deal with the crap) if it's not possible to meet both targets (compatibility and standard) something must decide ... imho 'usability', a program should work in a way and produce results which are correct for and can be handeled by 'normal' users, without exotic unversity grade to be happy about their special fp-knowledge. for those users it's 'not so easy' to accept arbitrarely choosen different handling of similar things, neither to understand the resulting calculation errors. either 'wall clock standard', a wall clock does not! show '00' for the seconds at 59:59,9 minutes, it shows 59 seconds till the minute is full (as it shows 59 for the minute till the hour is full), OR! intelligent rounding, but no mix-mess. (a friend of mine often quoted: 'either consquent or inconsequent, but not this or that as you like!') the 'wall clock standard' is especially made for those stupid users who'd have problems with rounded minutes? then we ought to give them 'wall clock standard' for seconds too. and of course for milliseconds, microseconds, nanoseconds and so on. how do caesium clocks tick in their last digit? rounding? or! we have to stick to compatibility, and round up. excels results are kohärent within themself? and users can calculate with them, former behaviour of calc was consistent in itself? and users could calculate with the results? and now we have something that fits to a 'standard', but breaks calculations in a spreadsheet and nags users ... ??? that's not real progress. rounding in stepped 'place value' systems with circulr iteration of the digits is 'not so easy', you have to care for the carry, but 'the standard' is to take the unrounded basic value, e.g. wednesday for '=weekday(43831,6;1)'? or 1 as a answer to 'give me the tens digit of 315,6 please'? if anyone (calc) deviates from that standard it should have good reasons, and results consistent within themself. ex$el deviates in a consistent way, calc is indifferent and deviating. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127476] Rounding behaviour of YEAR and SECOND function is inconsistent
https://bugs.documentfoundation.org/show_bug.cgi?id=127476 --- Comment #10 from Albrecht Müller --- The specification of the SECOND function: - 6.10.16 SECOND Summary: Extracts the second (the integer 0 through 59) from a time. This function presumes that leap seconds never exist. Syntax: SECOND( TimeParam T ) Returns: Number Constraints: None Semantics: Extract from T the second value, 0 through 59, as per a clock. Note that this returns an integer, without a fractional part. Note also that this rounds to the nearest second, instead of returning the integer part of the seconds. This is equal to: DayFraction = (T - INT(T)) HourFraction = (DayFraction * 24 - INT(DayFraction * 24)) MinuteFraction = (HourFraction * 60 - INT(HourFraction * 60)) Second = ROUND(MinuteFraction * 60) See also MONTH 6.10.13, DAY 6.10.5, HOUR 6.10.10, MINUTE 6.10.12, INT 6.17.2 -- Assume T = 59.6/86400 DayFraction = (59.6/86400 - INT(59.6/86400)) = 59.6/86400 - 0 = 59.6/86400 HourFraction = ((59.6/86400) * 24 - INT((59.6/86400) * 24)) = 59.6/3600 - INT(59.6/3600) = 59.6/3600 - 0 = 59.6/3600 MinuteFraction = ((59.6/3600) * 60 - INT((59.6/3600) * 60)) = 59.6/60 - INT(59.6/60) = 59.6/60 - 0 = 59.6/60 Second = ROUND((59.6/60) * 60) = ROUND(59.6) = 60 But: "Extracts the second (the integer 0 through 59) from a time." How is it possible to implement this specification correctly? The second problem with this specification is that it uses different rounding strategies for different functions. Therefore any implementation has to return inconsistent results in certain situations. The third is that the specified round down mechanism lets tiny round-off errors surface as arithmetic errors. Excel seems to use a carefully designed rounding strategy that handles the peculiarities of the floating point representation. Maybe this works roughly in way that I tried to explain in attachment 160356 (Trying to clarify clock vs duration time). This analysis inspired the workaround in bug 127170 comment 18 which hopefully enables date and time functions to return consistent results. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127476] Rounding behaviour of YEAR and SECOND function is inconsistent
https://bugs.documentfoundation.org/show_bug.cgi?id=127476 Eike Rathke changed: What|Removed |Added Status|RESOLVED|CLOSED -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127476] Rounding behaviour of YEAR and SECOND function is inconsistent
https://bugs.documentfoundation.org/show_bug.cgi?id=127476 Eike Rathke changed: What|Removed |Added Resolution|--- |WONTFIX Status|NEW |RESOLVED --- Comment #9 from Eike Rathke --- Only SECOND() is specified to round, MINUTE() and HOUR() are specified to not round. Yes rounding SECOND() is odd, complains go to Excel. Specifically you do not want to see 2000-01-01T00:00:00 if the value actually is 1999-12-31T23:59:59.9 ... and yes, obtaining 1999-12-31T23:59:00 for individual HOUR():MINUTE():SECOND() functions is not much better, not rounding at all would be expected, but again, complain with Excel. References: https://docs.oasis-open.org/office/OpenDocument/v1.3/cs01/part4-formula/OpenDocument-v1.3-cs01-part4-formula.html#HOUR https://docs.oasis-open.org/office/OpenDocument/v1.3/cs01/part4-formula/OpenDocument-v1.3-cs01-part4-formula.html#MINUTE https://docs.oasis-open.org/office/OpenDocument/v1.3/cs01/part4-formula/OpenDocument-v1.3-cs01-part4-formula.html#SECOND Btw, calculating with strings is luck and depends on the locale if it contains separators. If at all then use a date+time string in real ISO 8601 format which recognizes both 1999-12-31T23:59:59.9 1999-12-31T23:59:59,9 Closing as WontFix. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127476] Rounding behaviour of YEAR and SECOND function is inconsistent
https://bugs.documentfoundation.org/show_bug.cgi?id=127476 b. changed: What|Removed |Added Ever confirmed|0 |1 CC||newbie...@gmx.de Status|UNCONFIRMED |NEW --- Comment #8 from b. --- repro, year: 1999, second: 0, (the millenium problem doesn't apply, as well 1999-12-31 as 2000-01-01 belong to 20!, 21'th started at 2001-01-01, the error of celebrating the next millennium 12 months too early is somewhat bigger than the rounding errors in calc ...) -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127476] Rounding behaviour of YEAR and SECOND function is inconsistent
https://bugs.documentfoundation.org/show_bug.cgi?id=127476 --- Comment #7 from Albrecht Müller --- Created attachment 161049 --> https://bugs.documentfoundation.org/attachment.cgi?id=161049&action=edit A workaround for consistent rounding This spreadsheet is intended demonstrate the effects of the workaround described in bug 127170 comment 18 on bug 127476 -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127476] Rounding behaviour of YEAR and SECOND function is inconsistent
https://bugs.documentfoundation.org/show_bug.cgi?id=127476 Bug 127476 depends on bug 127477, which changed state. Bug 127477 Summary: Incomplete description of date & time functions in the help information https://bugs.documentfoundation.org/show_bug.cgi?id=127477 What|Removed |Added Status|RESOLVED|REOPENED Resolution|FIXED |--- -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127476] Rounding behaviour of YEAR and SECOND function is inconsistent
https://bugs.documentfoundation.org/show_bug.cgi?id=127476 Albrecht Müller changed: What|Removed |Added Status|NEEDINFO|UNCONFIRMED Ever confirmed|1 |0 --- Comment #6 from Albrecht Müller --- I uploaded two sample files: an .odt and an .xls version. No matter of if I open the .odt file with the an old 6.0.4.2 Windows version of LibreOffice or a 6.3.5.2 Linux version: both show 0 seconds and year 1999. I used the .xls version to open it with a Excel 97 version which is more than 20 years old. Excel showed the same values as LibreOffice but when I reentered the formula that calculates the year the value changed from 1999 to 2000. So I assume that the initial values are just those stored in the .xls file and the value 2000 comes from Excel’s own calculation. My explanation of this behaviour: More than 20 years ago Microsoft engineers were aware of the fact that when rounding 59.9 seconds to 60 and then mapping this to 0 seconds a carry should go to the minutes value and this carry may ripple down to the years position. LibreOffice seems to use no carry in this case. Consequence: The two programs disagree into which millenium the result should go. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127476] Rounding behaviour of YEAR and SECOND function is inconsistent
https://bugs.documentfoundation.org/show_bug.cgi?id=127476 --- Comment #5 from Albrecht Müller --- Created attachment 159790 --> https://bugs.documentfoundation.org/attachment.cgi?id=159790&action=edit xls version of the example file -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127476] Rounding behaviour of YEAR and SECOND function is inconsistent
https://bugs.documentfoundation.org/show_bug.cgi?id=127476 --- Comment #4 from Albrecht Müller --- Created attachment 159789 --> https://bugs.documentfoundation.org/attachment.cgi?id=159789&action=edit Example file to show inconsistent rounding -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127476] Rounding behaviour of YEAR and SECOND function is inconsistent
https://bugs.documentfoundation.org/show_bug.cgi?id=127476 Buovjaga changed: What|Removed |Added Ever confirmed|0 |1 CC||ilmari.lauhakangas@libreoff ||ice.org Status|UNCONFIRMED |NEEDINFO --- Comment #3 from Buovjaga --- Uploading an example document will not distract from anything. It is a basic requirement. The documentation requested in bug 127477 was implemented last year. Set to NEEDINFO. Change back to UNCONFIRMED after you have provided the document. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127476] Rounding behaviour of YEAR and SECOND function is inconsistent
https://bugs.documentfoundation.org/show_bug.cgi?id=127476 Bug 127476 depends on bug 127477, which changed state. Bug 127477 Summary: Incomplete description of date & time functions in the help information https://bugs.documentfoundation.org/show_bug.cgi?id=127477 What|Removed |Added Status|NEW |RESOLVED Resolution|--- |FIXED -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127476] Rounding behaviour of YEAR and SECOND function is inconsistent
https://bugs.documentfoundation.org/show_bug.cgi?id=127476 Albrecht Müller changed: What|Removed |Added See Also||https://bugs.documentfounda ||tion.org/show_bug.cgi?id=12 ||7334 CC||albrecht.muel...@astrail.de Status|NEEDINFO|UNCONFIRMED Ever confirmed|1 |0 --- Comment #2 from Albrecht Müller --- I think it is not useful to upload a sample file for the following reasons: 1) It is pretty easy to see the effect. Just enter the formulas into some cells of a spreadsheet and check what the results are. Maybe you have to set an option such that Calc accepts time values that are given as strings. 2) Uploading a file may distract from the real problem I want to address with this report. Note that I made this bug report to depend on bug 127477 to point out that you cannot fix this bug before this other bug has been fixed. Some background information: I have a 6.2.6.2 Ubuntu version available and discovered that I do not want to upgrade to this version as date & time arithmetic has changed and that about 50% of the results of trivial date & time calculations are now wrong. For details see bug 127334. So I learned that it is the specified behaviour of date & time functions like HOUR, MINUTE or SECOND to deliver pseudo random values that are correct in 50% of the cases and one off in the other 50%. Of course, the help information does not rule out this behaviour. Some other specification seems to exist that has exactly this behaviour as a consequence. So a change in behaviour I consider a critical bug has been classified as NOTABUG. Thus the real problem is that as a user I cannot know what these date & time functions are supposed to do. That's the reason for bug 127477. I think there is a lot of confusion about how the date & time arithmetic is intended to work which results in a couple of bug reports and fixes. This is the reason why I just uploaded an additional comment to bug 127477 that tries to clarify what I see a common source of confusion and unnecessary work. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127476] Rounding behaviour of YEAR and SECOND function is inconsistent
https://bugs.documentfoundation.org/show_bug.cgi?id=127476 m.a.riosv changed: What|Removed |Added Status|UNCONFIRMED |NEEDINFO CC||miguelangelrv@libreoffice.o ||rg Ever confirmed|0 |1 --- Comment #1 from m.a.riosv --- Please attach a sample file. BTW can you test with a newest version. 6.0 it's EOL, not new updates on it. -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
[Libreoffice-bugs] [Bug 127476] Rounding behaviour of YEAR and SECOND function is inconsistent
https://bugs.documentfoundation.org/show_bug.cgi?id=127476 Albrecht Müller changed: What|Removed |Added Depends on||127477 Referenced Bugs: https://bugs.documentfoundation.org/show_bug.cgi?id=127477 [Bug 127477] Incomplete description of date & time functions in the help information -- You are receiving this mail because: You are the assignee for the bug.___ Libreoffice-bugs mailing list Libreoffice-bugs@lists.freedesktop.org https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs
