[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 #3 from Eike Rathke <erack at redhat.com> ---
(In reply to Albrecht Müller from comment #2)
> Closing this
> bug as "NOTABUG" tells me that LibreOffices quality standards allow time
> calculation algorithms to deliver zero or one minute with equal probability
> when subtracting e.g. two minutes from three minutes.

You're mixing different things. Using a date+time floating point representation
where time is fractions of days with 0.5 == 12h is a de-facto standard in the
spreadsheet world implemented by all major spreadsheet applications. This is
how date+time input is stored as cell content and in fact you can format *any*
floating point value with a date+time display format and get *some* result.
Repeated addition and/or subtraction of floating point values like in the
sample document *will* lead to accumulated round-off errors. You'll get similar
results for column F of the sample document if you do the same calculations in
other spreadsheet applications.

However, here the difference is in the MINUTE() function, which is a wall clock
time function, i.e. returns values 0..59 and does not round anymore, same as a
clock does not display 12:35 if time is 12:34:56

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.


> Second: This problem affects all functions of this kind. A round-off error
> in the sub-milliseconds range may cause the year function to return the
> wrong year. The key problem is that usually integral numbers of time units
> (years, month, days, hours, minutes and seconds) are considered and
> therefore the calculations could be exact. However, LibreOffice uses a time
> representation that cannot guarantee an exact representation of the time
> units hours, minutes and seconds.

In an ideal world we could use nanoseconds as date+time representation and have
a distinct datetime type that's only converted when needed, however, that's not
how spreadsheet applications work due to their legacy of historically grown
data types and procedures.

> Third: I cannot trust the results of LibreOffices date/time calculations any
> more. Any legacy spreadsheets containing this kind of simple time
> calculations will deliver wrong results if opened with recent versions of
> Calc.

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?

-- 
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/6ce5c3cf/attachment-0001.html>