[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 #6 from Albrecht Müller <albrecht.mueller at astrail.de> ---
The discussion of bug 132083 ended declaring it as a duplicate of this one here
and referring to the CLOCK and DURATION time formats. So I try to contribute to
the clarification of this issue. Unfortunately I do not really understand what
CLOCK and DURATION time formats means. So I first try to explain what I
understood:

A CLOCK time format is just an alias for a format that uses rounding based on
the well known "Round down" principle, while a DURATION time format is just
another word for a rounding based on "Rounding to the nearest". So if you round
3 hours 45 minutes to an hours precision you will get 3 hours when using a
CLOCK time format and 4 hours when using a DURATION time format. There is an
easy way to get "Rounding to the nearest" when you have a "Round down"
mechanism: Add one half of the unit you want to round to and round the result
down. This gives rounding to the nearest. Example: 3 hours 45 minutes + 30
minutes are 4 hours 15 minutes. Round this down and you get 4 hours which is
the same result I get when I use a DURATION time kind of rounding on the
original value.

There seems to be a well established convention that the representation of
numbers in decimal format uses the "rounding to the nearest" principle, while
the presentation of time values is expected to use a "round down" mechanism. I
assume the reasons for this difference are like this:

Decimal numbers are often used in a context where you expect some errors, be it
measurement errors, round off errors or maybe other things. That’s why you need
some rounding that is robust against these errors. "Rounding to the nearest"
provides this feature: you may have a measured a value of 1.9 or 2.1. This does
not matter, both are roughly two, and that’s also the result of the rounding.

This is different when it comes to time values. Here you usually have some kind
of ticks of a clock and you count these ticks. So you deal with integral values
and there is no room for imprecise math like round off errors. We may start
counting seconds with zero, the 60th tick starts the first minute and resets
the seconds to zero again. If you are interested in the minutes value only you
may just discard the seconds. This results in a kind of "round down" behaviour.

Both rounding methods have points where the result of the rounding depends on
very small changes in the value to round. Rounding to the nearest is sensible
to changes if the value to round is near to the middle of two adjacent possible
rounding results, e.g. 2.499999 gets rounded to 2 while 2.499999 + 0.000002 =
2.500001 rounds to 3. The corresponding points of the round down methods are
the possible rounding results themselves: 0.999999 gets rounded to 0, while
0.999999 + 0.000002 = 1.000001 gets rounded to 1.

This property of the round down mechanism is what makes the wall clock time
kind of rounding so problematic. If used together with LibreOffice’s inherently
imprecise time representation and without some mechanism that mimics some kind
of countable clock ticks (see Mike Kaganski’s proposal in Bug 127334 comment 8)
you will always see a lot of results that are plain wrong despite the fact that
the underlying values are pretty close to those that are exact and correct. Bug
127334, bug 131151 and bug 132083 demonstrate this.

Based on this understanding of the problem I think that it is necessary to
decide about the way Mike Kaganski’s proposal should be used and to document
the results of this decision in the help information. This requires clarifying
details such as: Should LibreOffice use a single basic time unit or more than
one like Excel, what basic unit to use (1 second, 1 millisecond, 1 microsecond,
something else, e.g. a parameter of the spreadheet)? What are the tradeoffs of
those different basic units? How to achieve compatibility/interoperability with
older spreadsheets and with Excel? This would be easier if someone is able to
find some specification that describes how this old de facto industry standard
is supposed to work.

I don’t think that it is a good idea associating the [HH] and HH constructs
with different formatting strategies:
1) The CLOCK and DURATION formatting methods have not been documented yet. So
probably a lot of existing spreadsheets rely on the assumption that the same
rounding is applied no matter if you use [HH] or HH. A change would break these
spreadsheets.
2) As there is an easy way to get DURATION kind of rounding if you have CLOCK
type rounding there is no need to implement a DURATION kind of rounding. Maybe
a hint in the help information is enough.
3) The [HH] vs. HH construct is already used to control if the result should
show the total hours or the remainder of a division by 24. I think there is no
natural connection between this feature and the kind of rounding: All possible
four combinations of these features make sense. You may want to have wall clock
time values formatted using a "round to the nearest" format - semantics being
something like "about 3 o’clock". On the other hand age is a duration but this
is usually given in a "round down" way. Therefore I fear that bundling the two
concepts together will produce a lot of bug reports as users do not expect this
connection. So please keep separate things separate.

-- 
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/20200416/57379ea0/attachment.htm>