<html> <head> <base href="https://bugs.documentfoundation.org/"> </head> <body> <div> <a class="bz_bug_link bz_status_CLOSED bz_closed" title="CLOSED WONTFIX - Rounding behaviour of YEAR and SECOND function is inconsistent" href="https://bugs.documentfoundation.org/show_bug.cgi?id=127476#c10">Comment # 10</a> on <a class="bz_bug_link bz_status_CLOSED bz_closed" title="CLOSED WONTFIX - Rounding behaviour of YEAR and SECOND function is inconsistent" href="https://bugs.documentfoundation.org/show_bug.cgi?id=127476">bug 127476</a> from <a class="email" href="mailto:albrecht.mueller@astrail.de" title="Albrecht Müller <albrecht.mueller@astrail.de>"> Albrecht Müller</a> <pre>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 <a href="http://bugs.documentfoundation.org/attachment.cgi?id=160356" name="attach_160356" title="Trying to clarify clock vs duration time">attachment 160356</a> <a href="http://bugs.documentfoundation.org/attachment.cgi?id=160356&action=edit" title="Trying to clarify clock vs duration time">[details]</a> (Trying to clarify clock vs duration time). This analysis inspired the workaround in <a class="bz_bug_link bz_status_NEW " title="NEW - Clarify clock time (HH:MM, MM:SS, ...) and duration time ([HH]:MM, [MM]:SS, ...) formatting in help; (ignore the MM month vs minute discussion)" href="show_bug.cgi?id=127170#c18">bug 127170 comment 18</a> which hopefully enables date and time functions to return consistent results.</pre> </div> <hr> You are receiving this mail because: <ul> <li>You are the assignee for the bug.</li> </ul> </body> </html>