[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)

Fri Apr 17 20:37:10 UTC 2020

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

--- Comment #9 from Albrecht Müller <albrecht.mueller at astrail.de> ---
@jarko: I have several spreadsheets I use with different versions of
LibreOffice. If I open them with an old version, I see correct values. Using a
current version, I see wrong values. I use both versions to change the
spreadsheets. It does not matter from which version the spreadsheet comes from:
A current version displays the wrong values and I see the right results when I
open it with an old version.

This is consistent with my assumption that the rounding mechanism has been
changed such that it may display unexpected values even cases where the value
to display is the best possible approximation of the exact value. If this value
happens to be less than the exact value the round down mechanism will round it
to the next rounding point below. A user will experience a kind of pseudo
random behaviour showing about 50% correct and 50% wrong values. I don’t accept
the wall clock time argument here as the time difference in question is so
small that I never ever will notice this difference on any wall clock.

@Mike Kaganski: I fear explaining why I disagree with your short statement
"This is totally wrong" requires some lengthy explanation and that we are
discussing how to reinvent the wheel. There is a de facto industry standard
that is decades old and works in practice. It works despite the fact that it
uses floating point numbers which in general cannot represent time values
exactly. This standard has a lot of deficiencies but I think there is no room
for substantial improvement. Any change will break a lot of spreadsheets that
rely on the very features of this standard, e.g. the way rounding is done. So
we can only document how we understand the features of the standard but should
not do any substantial change.

Assume there is some user who wrote a spreadsheet some years ago. The results
should be in a format what you now call DURATION format but the standard only
offered what you now call CLOCK format. Knowing the rounding trick the user
will add 0.5 of the relevant unit before a value is displayed and will get the
expected result.

Would you do this user a favour if you change the rounding behaviour to the
DURATION rounding this person actually wants? No: After the change the
spreadsheet feeds the wrong values into the rounding mechanism and the user
will see wrong values and will probably spend a lot of time to analyse and to
fix the problem. Or worse: will not notice the problem and run into trouble due
to the use of this wrong information. How to handle a situation where some
users want to exchange spreadsheets but use different versions of LibreOffice?
Should they write macros that check the version of LibreOffice and change the
contents of the cells according to the rounding that the current version
offers?

Having to manipulate numbers to prepare them for display is a common problem
that is not limited to the formatting of time values: The available formatting
mechanisms may not provide the format you need. Therefore you manipulate the
values before you display them. You may multiply a number by 100 if you don’t
have a percent format. In other situations you may use functions like CEILING,
FLOOR and ROUND and a couple of others. For time calculations you might think
of using functions such as SECOND, MINUTE and the like. But I think their
semantics is so ill defined/documented that I don’t use them.

There may be a conceptual difference between duration and wall clock time. But
I think it is both a strength and a weakness of this industry standard that it
does not distinguish between these two concepts: Anything is a float and it is
up to you if you interpret this as a duration or as a point in time.

In my previous post I forgot to mention a few assumptions that I think should
be documented in the help information, provided I am correct: The time
representation is based on the assumptions that one day is 24 hours, one hour
is 60 minutes and one minute is 60 seconds. If I am not - the correct
information should be documented.

Due to these assumptions there is no need to distinguish between wall clock
time and duration. Unfortunately these basic assumptions are wrong - that’s why
they should be documented. There are days that are 25 hours, 23 hours or 24
hours + 1 second long, and there are minutes with a length of 61 seconds. This
happens on days when there is a switch between daylight saving time and
standard time or on days containing leap seconds. Now the distinction between
points in time and durations becomes relevant: It is not more the same if you
add one day or 24 hours.

Just a few hints how this kind of problems is solved elsewhere: I think Java
has what I would consider three generations of date and time systems. They
started with a java.util.Date class and some related classes such as
java.sql.Date. I think the basic concept is roughly the same as the date and
time system used in LibreOffice with the exception that they base their
representation on integral instead of floating point values. This way they
avoid the rounding issues inherent to a floating point representation.

They quickly discovered that this concept cannot support many time and date
features. So they developed a second generation of classes such as
java.util.Calendar and java.util.GregorianCalendar. These classes allow date
and time calculations that support time zones and daylight saving times. The
definitions of times zones and daylight saving times may change due to
political events. Therefore these classes need some kind of database and there
is a need to keep it current.

The package java.time contains what I consider a third generation of date and
time classes. Here they have classes such as java.time.Duration or
java.time.Instant which implement the distinction between durations and points
in time.

You may locate and study the javadocs of these and related classes and packages
to get an impression what is ahead of you if you really intend to implement
correct date and time calculations at a high level of precision. Note: They
seem not to change the semantics of existing classes as this would break
existing programs. They implemented new classes and declared old features
deprecated.

-- 
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/20200417/81d4c6d4/attachment.htm>