[Libreoffice-bugs] [Bug 127334] Regression: Incompatible changes in date/time arithmetic introduced between Version: 6.0.4.2 (x64) and version 6.2.6.2 (ubuntu)

[bugzilla-daemon at bugs.documentfoundation.org]

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

--- Comment #4 from Albrecht Müller <albrecht.mueller at astrail.de> ---
(In reply to Eike Rathke from comment #3)
> 
> Whether before and after calculations with values formatted as times the
> underlying floating-point value could be rounded to, for example,
> milliseconds is an implementation detail that maybe could be treated
> differently, but again may also yield unexpected results if they then are
> rounded and should not.
> 
I did this kind of calculations for years and Calc version 6.2.6.2 was the
first spreadsheet application where I noticed this problem. I always got exact
results despite the fact that I used floating point calculations that are
inherently inexact. There is an obvious way how this may work: The spreadsheet
applications limit the time resolution to some fixed value. Just for reference
I tried a twenty year old MS-Excel spreadsheet and discovered that this
application limits formatting of time values to a millisecond precision. If the
accumulated round-off errors of the floating point calculations remain below
half of the maximal resolution then you can identify the exact result. Based on
this exact result you can derive the exact values for the other units (seconds,
minutes etc.) too. There is no need to distinguish between concepts such as
duration or wall clock time. This distinction may be useful for formatting
purposes, however. If such a distinction is made this needs to be documented in
the help function.

The maximal resolution is _not_ an implementation detail but an essential
property of the time calculation mechanism. I think it is necessary to choose
this value carefully with respect to the underlying floating point arithmetic.
A coarse time resolution is robust against round-off errors. If you choose a
more fine grained resolution then the results will be more susceptible to
round-off errors. I think that the behaviour of Version 6.2.6.2 is roughly
equivalent to pushing this resolution to the precision of the underlying
floating point arithmetic. As a consequence the calculations return values that
are - from a users perspective - random values that are correct in about 50% of
the cases and one off in the other 50%.

> 
> What did it help that in older versions a time value of 23:59:59.99999 was
> displayed as 24:00:00.00 and the MINUTE() function returned 0 and if it was
> displayed as date+time it was displayed as the next day?

This was never a problem as I always got correct and consistent results even
when converting between different spreadsheet applications. I assume that these
applications round a value corresponding to 23:59:59.99999 to the nearest legal
value (probably exactly 1 in this case) before they do any date/time related
calculations.

-- 
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/20190905/87dfa9b5/attachment.html>