[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 #26 from Mike Kaganski <mikekaganski at hotmail.com> ---
(In reply to Albrecht Müller from comment #24)

The document is interesting, thanks (I hope the misinterpretation mentioned in
comment 2 is not intentional). However, it seems to either accidentally mix two
things, or tries to do that on purpose: it doesn't discuss the problem as
formatting problem, but rather as computational problem when performing
spreadsheet operations with date/time values.

It should be stated upfront, that this is not a useful approach. I don't think
it's possible that Calc starts processing "Date/Time" cells/values somehow
differently when performing calculations. The only useful discussion is about
how the results of normal floating-point calculations, using the unit of day,
without any intermediate conversion into any other kind of unit or data type
like integral number of small units, and without rounding (or different
post-processing) of the stored result, be represented on display. Summing,
multiplying, subtracting durations and/or times should continue to be the same
summation/multiplication/subtraction of underlying doubles. Which means that
the end result would still be in general off by some value...

But it's the display (date/time format) that we need to change. And so this is
the question about the final calculations when the result is converted to the
date/time format, when we should settle to some kind of rounding to some
fraction of time. So I refer to Bug 127334 comment 8, and this time, I suggest
to follow the Excel's sane decision:

1. To avoid problem with SECOND/MINUTE/HOUR/... that you rightfully mentioned
in the attachment 165304, given that there's no function giving the time parts
smaller than seconds, all the mentioned functions must round to nearest
integral seconds, and then calculate the resulting value of each of them. So
that for any given date/time value, the whole set of YEAR(val), MONTH(val),
DAY(val), HOUR(val), MINUTE(val), and SECOND(val) would form a reasonable set
of data, without a possibility of error of almost (-1 min).

2. Then, since these functions are reasonably expected to give the same data as
shown using "YYYY-MM-DD HH:MM:SS", the date/time format that does not include
fractions of seconds should be processed following the same rules on display:
rounding to nearest integral seconds, then calculating all the format
components from that value.

3. Only when displaying the date/time formats including fractions of seconds,
use some smaller fraction to round to. Please let's settle on fixed 1/1000 of a
second - it has reasonable robustness to allow tens (hundreds) of arithmetic
operations before floating-point operation errors might affect the display
value, and it consistent with other spreadsheet software.

4. Document all this.

-- 
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/8e1f207b/attachment.htm>