[Libreoffice-bugs] [Bug 127477] Incomplete description of date & time functions in the help information

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=127477

--- Comment #1 from Albrecht Müller <albrecht.mueller at astrail.de> ---
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.999999999999993 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.999999999999993 and 1.000000000000007 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.000000000000006.

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20190911/5d898a9e/attachment-0001.html>