[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 #20 from Albrecht Müller <albrecht.mueller at astrail.de> ---
The last sentence in the description of my workaround: The workaround cannot
easily extended to respect fractional parts as the SECOND function is specified
to round to the nearest second.

Thus this workaround is intended for cases where you deal with whole numbers of
seconds that may contain round-off errors. This seems to be a pretty common
case. What I expect to gain is to get consistent results from the date and time
functions such as SECOND, MINUTE etc. This workaround should also fix situation
where you see calculations such as 18 minutes - 8 minutes = 9 minutes without
the need to care about the wall clock vs. duration distinction (see bug
132083). The workaround is just a by-product of the analysis I tried in
attachment 160356 (Trying to clarify clock vs duration time). 

In my opinion date and time functions are closely related to formatting. Assume
you format some date and time value with no fractional part at the seconds
position. Then the value at the seconds position should be exactly the value
returned by the SECOND function for the same value. This should be true for the
other functions such as MINUTE, HOUR, DAY, MONTH and YEAR too. 

In this context (ignoring the fractions of seconds) your examples show the
correct behaviour:
2020-02-02 02:02:02.99 rounded to the nearest second is 2020-02-02 02:02:03
and 
2020-02-02 02:02:59.99 rounded to the nearest second is 2020-02-02 02:03:00

A few words to the results of the analysis in the attachment:

The specification of the date and time functions contains serious flaws.
Therefore I think it is impossible to implement a reasonable date and time
arithmetic that is based on this specification (see also bug 127476 comment
10). If you try you will need a scaring long help information explaining a lot
of unexpected behaviour.

Using different rounding strategies for wall clock time and durations may solve
problems of calculation such as 

       wall clock time - wall clock time → duration

       but not

       wall clock time + duration → wall clock time

as in the latter case the round down mechanism for wall clock time will
transform tiny round-off errors into clearly visible arithmetic errors. (See
bug 131151, I could not try my workaround in this case as the ROUND function
seems not to work in text tables)

There is a rounding strategy that should work in the same way for wall clock
time as well as for durations: 

1. Multiply the date and time value by the number or seconds in a day, i.e.
86400.

2. Round this value to the required number of digits in the fractional part of
the second. This step provides the best possible approximation and makes the
calculation stable against round-off errors.

3. Separate the number into an integer and a fractional part. The fractional
part is used to format the fractional part in the seconds value. The integer
part is used to calculate all date and time information such as seconds,
minutes, … year. As these calculations are based on exact integer values there
are no rounding problems in this part.

Note: You may get different integer values depending on the number of digits in
the fractional part. As a consequence _all_ date and time functions need a
parameter that tells them this number of digits. This parameter should default
to zero.

-- 
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/20200526/7865a189/attachment.htm>