[Libreoffice-bugs] [Bug 127477] Incomplete description of date & time functions in the help information
https://bugs.documentfoundation.org/show_bug.cgi?id=127477 --- Comment #10 from Albrecht Müller --- I think your patch does not address the rounding issues discussed at the beginning of this thread. The use of different rounding methods can produce quite different results. Therefore the help information should explain how date and time functions handle the rounding that is necessary due to the inherently inexact representation of time values. Note: There is a pending change of the open document specification that addresses this problem (see https://issues.oasis-open.org/browse/OFFICE-4094). -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 127477] Incomplete description of date & time functions in the help information
https://bugs.documentfoundation.org/show_bug.cgi?id=127477 --- Comment #9 from Commit Notification --- Olivier Hallot committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/help/commit/a6304ba5dd0efe2d5e2a16254aa9e7d2e74f5660 tdf#127477, tdf#131382 scope of function Minute -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 127477] Incomplete description of date & time functions in the help information
https://bugs.documentfoundation.org/show_bug.cgi?id=127477 Commit Notification changed: What|Removed |Added Whiteboard|target:6.5.0|target:6.5.0 target:7.4.0 -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 127477] Incomplete description of date & time functions in the help information
https://bugs.documentfoundation.org/show_bug.cgi?id=127477 Albrecht Müller changed: What|Removed |Added See Also||https://bugs.documentfounda ||tion.org/show_bug.cgi?id=13 ||3389 --- Comment #8 from Albrecht Müller --- I just discovered that LibreOffice seems to contain two different implementations of date and time arithmetic: One for Calc and maybe Writer, another for Basic. These implementations sometime return different values for the same calculations, see bug 133389. There are also independent descriptions of corresponding functions. Some examples: MINUTE function in Calc: https://help.libreoffice.org/6.4/en-US/text/scalc/01/func_minute.html Minute function in Basic: https://help.libreoffice.org/6.4/en-US/text/sbasic/shared/03030202.html?DbPAR=BASIC TIME function in Calc: https://help.libreoffice.org/6.4/en-US/text/scalc/01/func_time.html TimeSerial function in Basic: https://help.libreoffice.org/6.4/en-US/text/sbasic/shared/03030205.html?DbPAR=BASIC The description of the TimeSerial function explains how this function handles parameters that are outside their usual range. There is no such information in the description of the corresponding TIME function. Corresponding functions should behave in essentially the same way same. Therefore their descriptions should contain "See also" links referring to each other: Information that is missing in one the description of some function may be available in the description of the corresponding function. -- 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 127477] Incomplete description of date & time functions in the help information
https://bugs.documentfoundation.org/show_bug.cgi?id=127477 Albrecht Müller changed: What|Removed |Added Status|RESOLVED|REOPENED Resolution|FIXED |--- Depends on||127170 See Also||https://bugs.documentfounda ||tion.org/show_bug.cgi?id=13 ||2083, ||https://bugs.documentfounda ||tion.org/show_bug.cgi?id=13 ||1151 --- Comment #7 from Albrecht Müller --- A request for a sample file for Bug 127476 brought this bug to my attention again. I think the documentation has improved considerably but it still lacks some important information: 1) The relation between formatting and and a couple of date and time functions should be clarified. 2) Date and time function show some weird features which I think will surprise the unsuspecting user. Therefore this behaviour should be documented. The date and time functions are specified in a way that 2.1) they have to return the inconsistent values and 2.2) they are expected to return pseudo random results in pretty common use cases. Details to these points: ad 1) The relation between formatting and date and time functions is not so trivial as it seems: The the semantics of the formatting of date and time values has changed recently. To my knowledge this change is not documented yet but there is a discussion about this subject in bug 127170. Due to this change the same value can be formatted in several ways. The resulting representations may contain different values for corresponding time units. Therefore the help information should clarify which of these possible values the corresponding date and time function should return. It also should specify some workarounds for the case that the other values are needed and how to deal with legacy spreadsheets that may have used date and time arithmetic with different semantics. Ad 2) Maybe someone can analyse the specification (some links see comment #3) and confirm or refute my conclusions. If they are correct the help information should inform the users about a behaviour that they may not expect. Ad 2.1) This problem is the subject of bug 127476. The help information should also specify what to do if some user wants the carry to be handled in the Excel way. Ad 2.2) The internal representation of time values cannot represent date and time values exactly in general. It is not possible to avoid round of errors in floating point calculations. Most of the date and time functions are specified to use a round down method. All together this has the effect that doing elementary math with an integer number of time units will result in some pseudo random behaviour. After adding an integer number of minutes to another integer number of minutes the MINUTE function will probably show the correct result in 50% of the cases and a wrong number in the other 50%. I did not construct examples as I think this is a direct consequence of the specification. The formatting of date and time values seems to show a corresponding effect. As this is a more common use case the behaviour is already the subject of several bug reports for Calc (bug 127334 and bug 132083) and Writer (Bug 131151) Therefore I reopen this bug report. I make it dependent on bug 127170 as I think the solution of this bug determines if the points 1) and 2) can be fixed by a few remarks or it is necessary to provide lengthy explanations of the idiosyncrasies of LibreOffice’s date and time functions. Off topic: Is it correct that this bug depends on bug 80430? I suspect that this dependency should be the other way round but did not change it. Referenced Bugs: https://bugs.documentfoundation.org/show_bug.cgi?id=127170 [Bug 127170] Clarify clock time (HH:MM, MM:SS, ...) and duration time ([HH]:MM, [MM]:SS, ...) formatting in help; (ignore the MM month vs minute discussion) -- 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 127477] Incomplete description of date & time functions in the help information
https://bugs.documentfoundation.org/show_bug.cgi?id=127477 --- Comment #6 from Olivier Hallot --- (In reply to Olivier Hallot from comment #5) > I'm closing this bug as it has fixed the documentation part of the issue. > > The SECOND() rounding number issue raised here is to be fixed upstream of > documentation and is addressed in bug#127746. Once fixed, a companion > documentation bug can be opened. read bug127476 -- 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 127477] Incomplete description of date & time functions in the help information
https://bugs.documentfoundation.org/show_bug.cgi?id=127477 Olivier Hallot changed: What|Removed |Added Status|NEW |RESOLVED Resolution|--- |FIXED --- Comment #5 from Olivier Hallot --- I'm closing this bug as it has fixed the documentation part of the issue. The SECOND() rounding number issue raised here is to be fixed upstream of documentation and is addressed in bug#127746. Once fixed, a companion documentation bug can be opened. -- 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 127477] Incomplete description of date & time functions in the help information
https://bugs.documentfoundation.org/show_bug.cgi?id=127477 Commit Notification changed: What|Removed |Added Whiteboard||target:6.5.0 -- 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 127477] Incomplete description of date & time functions in the help information
https://bugs.documentfoundation.org/show_bug.cgi?id=127477 --- Comment #4 from Commit Notification --- Olivier Hallot committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/help/commit/efc2eba6081f95dbf83ea1776a563bbddaa13f68 tdf#127477 Incomplete description of date & time -- 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 127477] Incomplete description of date & time functions in the help information
https://bugs.documentfoundation.org/show_bug.cgi?id=127477 Olivier Hallot changed: What|Removed |Added Depends on||80430 Referenced Bugs: https://bugs.documentfoundation.org/show_bug.cgi?id=80430 [Bug 80430] [META] Documentation gap for new features -- 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 127477] Incomplete description of date & time functions in the help information
https://bugs.documentfoundation.org/show_bug.cgi?id=127477
--- Comment #3 from Albrecht Müller ---
(In reply to Olivier Hallot from comment #2)
>
> 2) YEAR("1999-12-31 23:25:59,6") is not a valid argument for the function,
> but
> YEAR(DATEVALUE("1999-12-31 23:25:59,6")) is. Besides we are 35 minutes
> before the hour in your example and it can't be the reason to round the
> year. Assuming it is a typo...
YEAR("1999-12-31 23:25:59,6") should have been YEAR("1999-12-31 23:59:59,6").
Sorry for this typo. This syntax works but it requires to set a switch in the
Calc options and the "de" locale.
>
> =YEAR(DATEVALUE("1999-12-31 23:59:59.9")) is 1999
> =YEAR(DATEVALUE("1999-12-31 23:59:59.999")) is 2000
I wanted to point to different thing: I think there is a bug in the
specification:
The specification of the SECOND function
(http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018202_715980110)
states: "Note also that this rounds to the nearest second, instead of returning
the integer part of the seconds". There is a problem in the last half second of
a minute. If you round this value, e.g. 59.6 seconds to to nearest second, you
will get 60 but the SECOND function is specified to return integer values in
the range 0 ... 59. Therefore something is wrong with this specification: If
SECOND return 60 this violates the requirement that the return value is in the
range 0...59. To ensure that the result is in the proper range you could take
the remainder of a division by 60. The specification does not contain this
operation but I think that this is tacitly assumed. Thus SECOND returns 0 in
this case.
The MINUTE function
(http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018194_715980110)
does not round to the next second. Thus a value corresponding to 59.6 seconds
is 0 minutes. Therefore 59.6 seconds corresponds to zero minutes and zero
seconds.
As all the other functions (YEAR, MONTH, DAY, HOUR, etc.) also do not round to
the nearest second it is possible to create this example where the problem
propagates up to the digit in the millennium position: So according to the
specification a value of "1999-12-31 23:59:59.6" should result in:
YEAR 1999, MONTH 12, DAY 31, HOUR 23, MINUTE 59, SECOND 0. A consistent value
would be YEAR 2000, MONTH 1, DAY 1, HOUR 0, MINUTE 0, SECOND 0.
This example is the content of bug 127476 which was intended to demonstrate of
what I called "funny results" in comment #1. In short: In order to avoid this
kind of inconsistencies all related date and time functions should use the same
rounding method.
> If they are not compliant, we must bring to the development for fixing.
I fear the problem appeared because the development made the behaviour
compliant to a broken specification.
> However I doubt the samples above can be considered a bug.
The line between bug or no bug seems to blur: It is confirmed behaviour that
simple date and time calculations will show pseudo random results that are
correct in 50% of the cases and one off in the other 50%(see bug 127334). I
would consider this as a bug but this behaviour arguably follows from the
specification. So this is not a bug?
--
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 127477] Incomplete description of date & time functions in the help information
https://bugs.documentfoundation.org/show_bug.cgi?id=127477 QA Administrators changed: What|Removed |Added Whiteboard| QA:needsComment| -- 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 127477] Incomplete description of date & time functions in the help information
https://bugs.documentfoundation.org/show_bug.cgi?id=127477
Olivier Hallot changed:
What|Removed |Added
Ever confirmed|0 |1
Status|UNCONFIRMED |NEW
--- Comment #2 from Olivier Hallot ---
Trying to sumarize.
1) The YEAR, MONTH... help pages are short in description and navigation
between similar topics. Help does not help... -> OK to improve.
2) YEAR("1999-12-31 23:25:59,6") is not a valid argument for the function, but
YEAR(DATEVALUE("1999-12-31 23:25:59,6")) is. Besides we are 35 minutes before
the hour in your example and it can't be the reason to round the year. Assuming
it is a typo...
=YEAR(DATEVALUE("1999-12-31 23:59:59.9")) is 1999
=YEAR(DATEVALUE("1999-12-31 23:59:59.999")) is 2000
Versão: 6.3.4.2
ID de compilação: 60da17e045e08f1793c57c00ba83cdfce946d0aa
Threads da CPU:8; SO:Linux 5.3; Realizador da interface: GL; VCL: kde5;
Local: en-US (pt_BR.UTF-8); Idioma de IU: pt-BR
Calc: threaded
3) We suppose the function(s) are compliant with the ISO-IEC 23600 standard
(aka Open Document Format from OASYS), therefore the Help pages contents can be
based on (e.g. YEAR)
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#__RefHeading__1018216_715980110
If they are not compliant, we must bring to the development for fixing.
4) as result of 3) we either flag the difference with MSExcel 97 or we just
ignore Excel 97 as not a standard in which we rely. Many Calc functions were
introduced to accommodate the "de-facto" excel standard and deviates from ODFF
standard.
5) If the standard ISO-IEC 23600 is not accurate on how rounding error are
handled... -> development.
However I doubt the samples above can be considered a bug.
--
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 127477] Incomplete description of date & time functions in the help information
https://bugs.documentfoundation.org/show_bug.cgi?id=127477 QA Administrators changed: What|Removed |Added Whiteboard|| QA:needsComment -- 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 127477] Incomplete description of date & time functions in the help information
https://bugs.documentfoundation.org/show_bug.cgi?id=127477 Albrecht Müller changed: What|Removed |Added See Also||https://bugs.documentfounda ||tion.org/show_bug.cgi?id=12 ||7473 -- 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 127477] Incomplete description of date & time functions in the help information
https://bugs.documentfoundation.org/show_bug.cgi?id=127477 --- Comment #1 from Albrecht Müller --- Here are some ideas how the behaviour of the various date & time functions could be specified. The first thing is to accept that you cannot know if some floating point number is intended to represent some point in time or a some duration. Thus there should be no attempt do distinguish between those concepts. Date & time functions deal with just another representation of floating point numbers. So I have no problem accepting that (assuming a proper value for the date base) 1900-1-1 times 1900-1-2 gives 1900-1-5 as this - using another representation - just states that 2 * 3 = 6. If you know what you are doing this is not a problem. They start if you don't because the help information does not tell you what you need to know. You can easily see that the source of many bug reports are probably some general problems of floating point number representation that are not specific to data & time calculations. Consider the common decimal notation. Instead of date & time functions like SECOND, MINUTE etc. we define a function DIGIT(x, n) which is intended to return an integer between 0 and 9 which is the nth digit of floating point value x. Let x=314.2 then you would expect the following results: DIGIT(x,2) = 3 DIGIT(x,1) = 1 DIGIT(x,0) = 4 DIGIT(x,-1) = 2 You easily get fooled into believing you can implement this function. The problem is that you might not even be aware that any such implementation is based on assumptions about the way what the "right" representation of this number is. This becomes clear if you add parameters that specify the underlying representation. You can for example add two parameters so you have a function DIGIT(x,n,m,r). The meaning of x and n is the same as before. m specifies the position of the least significant digit of the representation and r specifies the rounding method applied to calculate this digit. Values could be N, D and U to specify rounding to the nearest value, rounding down or up respectively. Just a few examples for the effect of these parameters: DIGIT(x,2,2,D) = DIGIT(x,2,2,N) = DIGIT(x,2,1,N) = DIGIT(x,2,1,U) = DIGIT(x,2,1,D) = 3 as this corresponds to the first digit of 300, 300, 310, 320 and 310 respectively. DIGIT(x,2,2,U) = 4 as rounding up 314,2 up at the most significant position gives 400. If you assume that m is less than -2 then the exact values of these additional parameters don't matter. You always get the same results. It is this very property which makes things difficult as you might assume that these parameters don't matter if you are precise enough. Unfortunately you are right in most cases so it may take a long time until you stumble on values like 0.993 where even the most significant digit depends on the position of the least significant digit and what kind of rounding you applied to get this digit. On the other hand this can become a common problem if you do mathematical operations with floating point numbers that don't have an exact representation and you apply D or U rounding. Under these circumstances values like 0.993 and 1.007 are quite common and you will encounter a kind of pseudo random behaviour of your calculations. This behaviour is caused by minimal round-off errors. Add for example 0 and 1, which actually are the these two numbers rounded down, you will get 2, but 1 if the second number actually is 1.006. I think it does not require additional explanation why you may get funny results if you calculate the digits of some number with different values for m and r and use these digits to compose a representation of this number. I hope that this analysis of the DIGIT function helps to understand the common root of many problems that plague date & time functions. There is no "right" precision or rounding behaviour that fits all floating point calculations and that's true for date & time calculations too. Therefore the date & time functions should use some information that plays the role of the m and r parameters of this DIGIT function. Of course it should be documented what this information is and how to control the relevant values to give both developers and users a chance to know what they are doing. -- 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 127477] Incomplete description of date & time functions in the help information
https://bugs.documentfoundation.org/show_bug.cgi?id=127477 Albrecht Müller changed: What|Removed |Added See Also||https://bugs.documentfounda ||tion.org/show_bug.cgi?id=11 ||8800 -- 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 127477] Incomplete description of date & time functions in the help information
https://bugs.documentfoundation.org/show_bug.cgi?id=127477 Albrecht Müller changed: What|Removed |Added Blocks||127334, 127476 CC||albrecht.muel...@astrail.de Referenced Bugs: https://bugs.documentfoundation.org/show_bug.cgi?id=127334 [Bug 127334] Regression: Incompatible changes in date/time arithmetic introduced between Version: 6.0.4.2 (x64) and version 6.2.6.2 (ubuntu) https://bugs.documentfoundation.org/show_bug.cgi?id=127476 [Bug 127476] Rounding behaviour of YEAR and SECOND function is inconsistent -- 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
