[Libreoffice-bugs] [Bug 136615] New: Re-consider date/time parts calculation for functions and formatting

[bugzilla-daemon at bugs.documentfoundation.org]

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

            Bug ID: 136615
           Summary: Re-consider date/time parts calculation for functions
                    and formatting
           Product: LibreOffice
           Version: unspecified
          Hardware: All
                OS: All
            Status: UNCONFIRMED
          Severity: normal
          Priority: medium
         Component: Calc
          Assignee: libreoffice-bugs at lists.freedesktop.org
          Reporter: mikekaganski at hotmail.com
                CC: albrecht.mueller at astrail.de, erack at redhat.com

Created attachment 165328
  --> https://bugs.documentfoundation.org/attachment.cgi?id=165328&action=edit
A sample of wrong round-tripping of date/time value

As discussed in Bug 127334 comment 8, bug 127170 (attachment 165304; bug 127170
comment 26) etc., there are some serious problems around date and time parts as
calculated using spreadsheet functions YEAR/MONTH/DAY/HOUR/MINUTE/SECOND, and
displayed using date/time format strings like "YYYY-MM-DD HH:MM:SS" (and any
variations, including decimal parts of second); both in old versions of Calc,
and in current versions, that had numerous tweaks related to how dates/times
and durations are displayed.

Current implementation of YEAR/MONTH/DAY/HOUR/MINUTE/SECOND spreadsheet
functions is consistent with ODF [1], but the standard itself is obviously
wrong in its wording regarding these functions. E.g., MINUTE is defined there
to be calculated discarding minute fraction from (hour fraction multiplied by
60), while SECOND is defined as rounding (minute fraction multiplied by 60) to
nearest whole number, but at the same time, being in [0..59]. This necessarily
produces wrong set of YEAR/MONTH/DAY/HOUR/MINUTE/SECOND values for any datetime
with seconds between 59.5 and 60.0: result of MINUTE would be as if the next
minute is not yet started, while result of SECOND would be as if it has already
started. See attachment for an example of the problem.

At the same time, there is much confusion and disagreement around how
date/times are displayed, where users don't realize that underlying data format
for datetime is double, or where users disagree to account for the tiny
floating-point calculation errors. Given the current distinction between
display of points in time vs duration formats, the inherent imprecision of
floating point operations may result in showing e.g. 9 minutes where users
expect 10 (just because the closest double happened to be slightly less than
exact value). It also looks natural to expect that a date string (using a
date/time format string) represents the same date parts as returned from
corresponding spreadsheet functions on the same value.

To create a consistent and robust set of rules for calculation of date/time
parts from a serial date (or a duration), the proposal is the following:

1. In OpenFormula specification, change definition of SECOND to mention
explicitly that the result is taken modulo 60, like this:
Second=MOD(ROUND(T*86400);60).

2. In OpenFormula specification, change definition of
YEAR/MONTH/DAY/HOUR/MINUTE (and others that might be affected) to account for
the seconds rounding. Example for MINUTE:
Minute=(MOD(ROUND(T*86400);3600)-SECOND(T))/60

3. Make date/time format strings follow the same rules, *unless* the format
string includes decimal seconds part.

4. In case a date/time format string includes decimal seconds part, it should
follow similar rules, with the exception that rounding should be performed not
to seconds, but to 1/1000th of a second. This should give enough precision to
have robustness against displaying rounding errors after tens/hundreds of
arithmetical operations on times/durations in the representable date/time
range. For the excess decimal second digits, zeroes must be output. The idea is
that normally rounding to a second is enough for widest range of cases; only
when explicitly requested, the sub-second precision should be used, still with
precautions to provide robust results, not easily affected by inherent
imprecision of floating-point operations and representation.

5. Duration formats should follow same or similar set of rules; whether they
should round least significant displayed part other than second (as opposed to
truncation) is not covered by the proposal, only that the values should be
calculated based on original double converted to whole numbers of seconds or
milliseconds (using rounding), as required by the format string and rules
above.

This proposal is aimed to be compatible with rules used for the same operations
in MS Excel, be predictable and sane, address most expectations, and allow
creating clear documentation about implementation.

[1]
https://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html#SECOND

-- 
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/20200909/3d8b06ce/attachment.htm>