https://bugs.documentfoundation.org/show_bug.cgi?id=136615
--- Comment #37 from Albrecht Müller <albrecht.muel...@astrail.de> --- (In reply to Eike Rathke from comment #31) > > (In reply to Regina Henschel from comment #30) > > @Eike: The attachment lists the results from several application. These > > results show, that a standardization is indeed needed. Since ODF 1.4 has the > > 'round' variant, LibreOffice needs to provide a function according this > > standard. > As mentioned elsewhere, I consider that rounding specification *WRONG* and > advise *AGAINST* it. Some major implementations (Excel, Gnumeric, Google) > doing it wrong to be compatible with Excel doesn't make it right. > > > Eike, do you have an idea how we can do it? > No. You can have either or, not both. Or implement the bad specification and > introduce another set of functions like CLOCK.SECOND() and so on to obtain > the unrounded matching wall clock values. > > > The problem I see is, > > that existing documents would have a different result in ODF 1.4 than in ODF > > 1.3, if we simply change the algorithms. > Yes, that may happen. This may not be as bad as it seems. Implementers not related to LibreOffice may have noticed that the algorithm given in the specification of the SECOND function returns 60 if the argument of the function happens to fall in the last half second of a minute but the specification also requires that the return values are in the range of 0 to 59. Thus it is impossible to implement the SECOND function in the way it is specified. They may also have noticed that the round down method used in the specification of the MINUTE and other functions have the unpleasant properties which I tried to explain in comment #32 and which surfaced in Bug 127334 (which in my opinion is not fixed – using an empirically determined unsharpness to silence an alarm introduces just another incompatible change but does not restore compatibility). Thus they probably decided to ignore this part of the specification altogether and to implement these functions in the Excel way. As I understand it the new specification tries to describe how this Excel way works. Therefore nothing will change for the users of these implementations. The only change is that these implementations will become conformant to the new specification. The situation is different for users of LibreOffice: Several versions of LibreOffice that produce different results already exist. Maybe the users will appreciate it that there will be a hopefully last change towards a de facto industry standard that seems to have been stable for more than a quarter of a century. I think there is a deficiency that is common to both the old and the new specification: They both are like an uncommented piece of code as they specify what has to be done but not why. An important “why” is compatibility with Excel. Calc users should be able to easily exchange calculations with Excel users. This compatibility also preserves the value of the knowledge about the strengths and weaknesses of Excels method when another software is used. There are good reasons why Excels way of doing this kind of calculations is so popular. It is a simple and clever compromise that covers the requirements of a lot of use cases: Time is often seen as a continuous physical quantity which has a wide dynamic range. The time the light needs to travel a distance of the size of an atom is quite short. The age of the universe is an example of a much longer timespan. Given a suitable base unit IEEE 754 double values can represent both time values. IEEE 754 double arithmetic also provides the mathematical operations needed for calculations with time in physical context. On the other hand, date and time calculations use the idea of a discrete time where time is measured by counting equidistant clock ticks. This allows the use of exact integer arithmetic which avoids the round off errors of floating point arithmetic. The dynamic range is much smaller. Times shorter than a second or longer than a few 1000 years are rarely used in this context. Excel seems to round to the nearest integer multiple of some base unit (usually a second, sometimes microseconds, maybe other units) when translating from continuous to discrete time. I think this rounding method has an interesting property: As long as the round off errors are somewhat less than half of the base unit it does not matter if you use inexact floating point calculations and round the result or if you round the arguments first and use exact integer arithmetic afterwards. In both cases the result will be the same. Due to the precision of double values round off errors that exceed half of a second almost never occur in everyday calculations. Thus users don’t have to care about round off errors and can use floating point calculations as if floating point arithmetic would provide exact calculations with hours, minutes and seconds. Excel assumes that a day always has 24 hours, an hours always has 60 minutes and a minute 60 seconds. This assumption corresponds to a common perception, makes a lot of calculations with date and time quite simple and works for many use cases where leap seconds or switching between daylight saving and standard time does not matter. I think that using the same rounding to the nearest strategy it would be possible to build date and time calculations that supports daylight saving time and leap seconds. However, if you do so date and time calculations will become much more complicated as you have to treat days, hours and minutes as quantities of indeterminate length in a similar way as you have to treat months or years. The gain of this kind of date and time arithmetic is probably not worth the trouble. The specification should also point out that it uses at least one incompatible other method to translate from continuous time to discrete time. Consider for example “Procedure A” for the calculation of the “Basis” that is used in financial calculations and which is specified in https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#Basis-ProcedureA. Here date values are truncated, i.e. rounded down to the start of a day. The authors of this specification were probably not aware that date values may be a few microseconds less than the exact start of the intended day. This can happen when the values are the result of some floating point calculations that contain round off errors. In this case the truncation operation drops a complete day due to this tiny error. A naive user may wonder why financial functions can return different values for the (in the sense of YEAR, MONTH and DAY functions according to the new specification) same arguments. -- You are receiving this mail because: You are the assignee for the bug.
