[Libreoffice-bugs] [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)

[bugzilla-daemon at bugs.documentfoundation.org]

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

--- Comment #13 from Albrecht Müller <albrecht.mueller at astrail.de> ---
A key point of the error recovery mechanism is to do most of the internal
calculations on integer values. It may be surprising simple:

It uses two parameters:
rv:  This is the raw floating point value that is feed into some date and time
function or into the formatter. 

n: This is the number of digits in the fractional part of the seconds
representation. This value is zero if used for date and time functions such as
SECOND, MINUTE etc.

At first we calculate another floating point value that prepares the rest of
the calculations. It is essentially the value rv expressed as multiples of
seconds instead of days and rounded to the appropriate number of decimal
digits. Assuming we have a function round(v,m) that rounds v to m decimal
digits after the decimal separator the prepared value pv is:

pv = round (rv * 24 * 60 * 60, n)

We use this value to get the recovered number of seconds rs. This is simply the
greatest integer that is less than or equal to pv. Thus assuming there is a
function floor that achieves this:

rs = floor (pv)

We also calculate a fractional part fp. This is a floating point value with the
property 0 <= fp < 1. This value is used to format the fractional part of the
seconds:

fp = pv - rs

If you use rs as the basis for all date and time functions (including such
functions that calculate the year, month etc, doing integer math only) then the
results should be consistent and exact.

Due to the rounding the results should be pretty robust against round off
errors. The round off errors usually will be well below the milliseconds range.
So you have to accumulate a lot of them to get the second wrong. I think that
is how Excel used to work for more than 20 years and old versions of
LibreOffice did. Unfortunately this does not conform with the specification
which is the basis of the current LibreOffice implementation. According to this
specification round off errors that I guess are in the range of 0.0000001
seconds decide which minute you see in your spreadsheet. The developers have no
chance to implement something compliant which shows reasonable behaviour.

I got a new insight with respect to the CLOCK vs. DURATION formatting debate.
Such a distinction is necessary in the context of negative values, and here it
is reasonable to tie this distinction to the use of [HH] or HH. But it is
irrelevant in the context of the arithmetic errors where it was used. 

Example: Take the value -1/32. This corresponds to -45 minutes and it should
have an exact floating point representation. Formatting this as HH:MM gives
"23:15" while [HH]:MM gives "-00:45". Both results make sense. The first format
interprets the value as "1899-12-29 23:15:00" and returns the hours and
minutes. The second format interprets the very same value a 45 minutes that go
back in time. OK: "1899-12-29 23:15:00" is actually 45 minutes before the
reference point "1899-12-30 00:00:00".

I think the cause of your problem in bug 132083 is that due to the missing
error recovery mechanism tiny round off errors will surface as arithmetic
errors. I also saw an example ( Bug 131151) that shows the same kind of errors
at a place where the use of CLOCK rounding is semantically correct. In a common
use case where the time values are between 0:00:00 and 23:59:59 it should not
matter if you use [HH] or HH.

So I think there is no need to change the internal representation. What is
necessary is to develop a clear concept of how the date and time arithmetic is
supposed to work, get the specification right and change help information and
implementation accordingly.

-- 
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/20200423/0173a1a8/attachment-0001.htm>