[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

Albrecht Müller <albrecht.mueller at astrail.de> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
              Alias|                            |BrokenDateTimeSpec

--- Comment #6 from Albrecht Müller <albrecht.mueller at astrail.de> ---
(In reply to Eike Rathke from comment #1)

> The MINUTE() function was changed to return the integer wall clock minute
> not rounded, as specified by ODF OpenFormula
> https://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#MINUTE

Some further analysis made mit think that this specification is really broken.
I doubt that the creators of this spec were aware of the consequences.

Some observations:
-
https://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#SECOND
This specification states "This function presumes that leap seconds never
exist." I did not find a reference where this is explicitly stated but I think
that the specification also ignores time zones and daylight saving time. The
combined effects of this omissions is that time and date calculations may be
wrong by more than 25 hours by design. Given those limitations I think it makes
sense that the de-facto standard you mentioned does not consider a time
resolution better than one second. Thus this specification of the SECOND
function specifies that the result is rounded to the nearest second.

-
https://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#DateTime
Here DateTime is defined: "DateTime is a subtype of Number. It is a Date plus
Time." So obviously adding date and time values is an intended and common use.
The problem here is that adding and subtracting date and time values may lead
to round-off errors. For example: On my machine the formula

= ((("2019-09-17" + "0:0:1") - "2019-09-17") - "0:0:1")/"0:0:1"

has the result 2,33761966278142E-07. Combining a date and a time into a
DateTime value and separating the components again leads to a loss of
significance which is in the order of magnitude of a fraction of a microsecond.
Given the general limitations of the date and time arithmetic this error should
not matter. 

-  
https://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#MINUTE
The specification of the MINUTE function does not require any rounding to the
nearest second. So consider some result that should be exactly the start of
some day but which is a little bit off. So the time fraction may be something
like "23:59:59.999" or "00:00:00,001". The seconds part will round to zero in
both cases (actually in the first case it should round to 60 seconds but I
assume that the modulo is taken. The specification is contradictory is this
point: ROUND(59.999) gives 60 but the function is supposed to return values
between 0 and 59 ). Rounding down the minutes part gives 59 or 0. The combined
result corresponds to "...:59:00" or "...:00:00" respectively. The little error
of two microseconds is amplified to an error of one minute (or 59 minutes
depending on the point of view). This problem is not restricted to the MINUTES
functions. See bug 127476 for an example where this effect may give you the
wrong millennium.

A key problem is that time and date values that use integral values for year,
month, day, hour, minute and seconds are located exactly at the boundaries of 
time intervals. Tiny round-off errors cause the values to be located on either
side of the boundary. The specified rounding method amplifies this effect so
the users see a kind of random results. I cannot believe that this is the
behaviour the authors of the specification had in mind.

I think there is a way to avoid the pseudo random behaviour and to get
consistent results: All functions that calculate properties of date and time
values such as YEAR, MONTH, DAY, HOUR, MINUTE or SECOND (there may be a couple
of other such functions) should operate on a value that is rounded to the
nearest second. For background information see 
https://bugs.documentfoundation.org/show_bug.cgi?id=127477#c1 .

-- 
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/20190917/b591c338/attachment.html>