Resolves: tdf#119533 reintroduce time rounding but cut, tdf#118800 follow-up

 This is a combination of 2 commits.

Resolves: tdf#119533 reintroduce time rounding but cut, tdf#118800 follow-up

Regression from

    commit c69e7266916ac1b8917477fb4eccdb9098da5792
    CommitDate: Thu Jul 19 14:01:30 2018 +0200

        tdf#118800 fix rounding error in Calc function HOUR, MINUTE, SECOND.

Rounding was only an error if it produced a value of a full day in
seconds, or if it otherwise led to an inappropriately rounded-up
individual value, but in general some rounding is necessary.

Instead of omitting rounding completely, basically round to
nanoseconds and then do not round individual hour,minute,second
values but instead truncate to the next magnitude so 23:59:59.9999
gives 23h59m59s instead of 24h0m0s

Reviewed-on: https://gerrit.libreoffice.org/59677
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Jenkins
(cherry picked from commit 273b3e10eab70ebc084cb62568bd699fddfb376e)

Shortcut small negative values to 0:0:0, tdf#119533 tdf#118800 follow-up

... instead of letting them end up as 24:0:0

Reviewed-on: https://gerrit.libreoffice.org/59699
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Eike Rathke <erack@redhat.com>
(cherry picked from commit 98cb91686901dc0133c5c23dc5658d9623dbd436)

0212a2b422a931a24fd2748aa2826a5b60d2a397

Change-Id: I93df1aa54212c1b8816237c9467f270ed28a3f1f
Reviewed-on: https://gerrit.libreoffice.org/59678
Tested-by: Jenkins
Reviewed-by: Caolán McNamara <caolanm@redhat.com>
Tested-by: Caolán McNamara <caolanm@redhat.com>

1 files changed, 50 insertions, 12 deletions

diff --git a/sc/source/core/tool/interpr2.cxx b/sc/source/core/tool/interpr2.cxx
index debe3509eb44..eb5352e843ae 100644
--- a/sc/source/core/tool/interpr2.cxx
+++ b/sc/source/core/tool/interpr2.cxx
@@ -142,28 +142,66 @@ void ScInterpreter::ScGetDay()
     PushDouble(static_cast<double>(aDate.GetDay()));
 }
 
+/* TODO: move this to tools::Time so also SvNumberFormatter and everything else
+ * can use it and all display the same values. */
+static void lcl_getHourMinuteSecond( double fTimeInDays, sal_Int32& nHour, sal_Int32& nMinute, sal_Int32& nSecond )
+{
+    const double fTime = fTimeInDays - rtl::math::approxFloor(fTimeInDays); // date part absent
+
+    // If 0 then full day (or no day), shortcut.
+    // If < 0 then approxFloor() effectively returned the ceiling (note this
+    // also holds for negative fTimeInDays values) because of a near identical
+    // value, shortcut this to a full day as well.
+    // If >= 1.0 (actually == 1.0) then fTimeInDays is a negative small value
+    // not significant for a representable time and approxFloor() returned -1,
+    // shortcut to 0:0:0, otherwise it would become 24:0:0.
+    if (fTime <= 0.0 || fTime >= 1.0)
+    {
+        nHour = nMinute = nSecond = 0;
+        return;
+    }
+
+    // In seconds, including milli and nano.
+    const double fRawSeconds = fTime * DATE_TIME_FACTOR;
+
+    // Round to nanoseconds, which is the highest resolution this could be
+    // influenced by.
+    double fSeconds = rtl::math::round( fRawSeconds, 9);
+
+    // If this ended up as a full day the original value was very very close
+    // but not quite. Take that.
+    if (fSeconds >= tools::Time::secondPerDay)
+        fSeconds = fRawSeconds;
+
+    // Now do not round values (specifically not up), but truncate to the next
+    // magnitude, so 23:59:59.99 is still 23:59:59 and not 24:00:00 (or even
+    // 00:00:00 which Excel does).
+    nHour = fSeconds / tools::Time::secondPerHour;
+    fSeconds -= nHour * tools::Time::secondPerHour;
+    nMinute = fSeconds / tools::Time::secondPerMinute;
+    fSeconds -= nMinute * tools::Time::secondPerMinute;
+    nSecond = fSeconds;
+}
+
 void ScInterpreter::ScGetMin()
 {
-    double fTime = GetDouble();
-    fTime -= ::rtl::math::approxFloor(fTime);       // date part absent
-    long nVal = static_cast<long>(::rtl::math::approxFloor(fTime*DATE_TIME_FACTOR)) % ::tools::Time::secondPerHour;
-    PushDouble( static_cast<double>(nVal / ::tools::Time::secondPerMinute) );
+    sal_Int32 nHour, nMinute, nSecond;
+    lcl_getHourMinuteSecond( GetDouble(), nHour, nMinute, nSecond);
+    PushDouble( nMinute);
 }
 
 void ScInterpreter::ScGetSec()
 {
-    double fTime = GetDouble();
-    fTime -= ::rtl::math::approxFloor(fTime);       // date part absent
-    long nVal = static_cast<long>(::rtl::math::approxFloor(fTime*DATE_TIME_FACTOR)) % ::tools::Time::secondPerMinute;
-    PushDouble( static_cast<double>(nVal) );
+    sal_Int32 nHour, nMinute, nSecond;
+    lcl_getHourMinuteSecond( GetDouble(), nHour, nMinute, nSecond);
+    PushDouble( nSecond);
 }
 
 void ScInterpreter::ScGetHour()
 {
-    double fTime = GetDouble();
-    fTime -= ::rtl::math::approxFloor(fTime);       // date part absent
-    long nVal = static_cast<long>(::rtl::math::approxFloor(fTime*DATE_TIME_FACTOR)) / ::tools::Time::secondPerHour;
-    PushDouble(static_cast<double>(nVal));
+    sal_Int32 nHour, nMinute, nSecond;
+    lcl_getHourMinuteSecond( GetDouble(), nHour, nMinute, nSecond);
+    PushDouble( nHour);
 }
 
 void ScInterpreter::ScGetDateValue()