[Libreoffice-bugs] [Bug 127476] Rounding behaviour of YEAR and SECOND function is inconsistent

[bugzilla-daemon at bugs.documentfoundation.org]

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

--- Comment #10 from Albrecht Müller <albrecht.mueller at astrail.de> ---
The specification of the SECOND function:

-----------------
6.10.16 SECOND

Summary: Extracts the second (the integer 0 through 59) from a time. This
function presumes that leap seconds never exist.

Syntax: SECOND( TimeParam T )

Returns: Number

Constraints: None

Semantics: Extract from T the second value, 0 through 59, as per a clock. Note
that this returns an integer, without a fractional part. Note also that this
rounds to the nearest second, instead of returning the integer part of the
seconds. This is equal to:

DayFraction = (T - INT(T))

HourFraction = (DayFraction * 24 - INT(DayFraction * 24))

MinuteFraction = (HourFraction * 60 - INT(HourFraction * 60))

Second = ROUND(MinuteFraction * 60)

See also MONTH 6.10.13, DAY 6.10.5, HOUR 6.10.10, MINUTE 6.10.12, INT 6.17.2
--------------

Assume T = 59.6/86400
DayFraction = (59.6/86400 - INT(59.6/86400)) 
   = 59.6/86400 - 0 
   = 59.6/86400
HourFraction = ((59.6/86400) * 24 - INT((59.6/86400) * 24)) 
   = 59.6/3600 - INT(59.6/3600) 
   = 59.6/3600 - 0 
   = 59.6/3600
MinuteFraction = ((59.6/3600) * 60 - INT((59.6/3600) * 60)) 
   = 59.6/60 - INT(59.6/60) 
   = 59.6/60 - 0 
   = 59.6/60
Second = ROUND((59.6/60) * 60) 
   = ROUND(59.6) 
   = 60
But: "Extracts the second (the integer 0 through 59) from a time."

How is it possible to implement this specification correctly?

The second problem with this specification is that it uses different rounding
strategies for different functions. Therefore any implementation has to return
inconsistent results in certain situations.

The third is that the specified round down mechanism lets tiny round-off errors
surface as arithmetic errors.

Excel seems to use a carefully designed rounding strategy that handles the
peculiarities of the floating point representation. Maybe this works roughly in
way that I tried to explain in attachment 160356 (Trying to clarify clock vs
duration time). This analysis inspired the workaround in bug 127170 comment 18
which hopefully enables date and time functions to return consistent results.

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