Bug 125099 - Rounding of durations displayed as wall clock time.
Summary: Rounding of durations displayed as wall clock time.
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.2.0.0.beta1+
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:6.3.0 target:6.2.5 target:7.1.0
Keywords: bibisected, bisected, regression
Depends on:
Blocks: Calculate
  Show dependency treegraph
 
Reported: 2019-05-03 08:50 UTC by Kim N. Jensen
Modified: 2020-09-08 15:12 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Sample spreadsheet (9.91 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-05-03 08:52 UTC, Kim N. Jensen
Details
Screendump from Calc 6.0 (7.56 KB, image/png)
2019-05-03 08:53 UTC, Kim N. Jensen
Details
Screendump from Calc 6.2 (7.71 KB, image/png)
2019-05-03 08:54 UTC, Kim N. Jensen
Details
How it looks in LibreOffice 7.1 master (164.10 KB, image/png)
2020-09-06 12:03 UTC, Xisco Faulí
Details
Sample spreadsheet 1 (12.09 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-09-08 15:10 UTC, Albrecht Müller
Details
Screendump from Calc 6.0.4.2 (8.43 KB, image/png)
2020-09-08 15:11 UTC, Albrecht Müller
Details
Screendump from Calc 6.4.5.2 (13.73 KB, image/png)
2020-09-08 15:12 UTC, Albrecht Müller
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kim N. Jensen 2019-05-03 08:50:46 UTC
Description:
In Calc 6.0 this calculation 11:24-08:00 gives the (correct) result 3:24:00 - in Calc 6.2 the result is 3:23:59.

Attached screendumps from 6.0, 6.2 and a sample spreadsheet.





Actual Results:
In Calc 6.0 this calculation 11:24-08:00 gives the (correct) result 3:24:00 - in Calc 6.2 the result is 3:23:59.

Expected Results:
The expected result would be 3:24:00


Reproducible: Always


User Profile Reset: No



Additional Info:
Comment 1 Kim N. Jensen 2019-05-03 08:52:10 UTC
Created attachment 151148 [details]
Sample spreadsheet
Comment 2 Kim N. Jensen 2019-05-03 08:53:23 UTC
Created attachment 151149 [details]
Screendump from Calc 6.0
Comment 3 Kim N. Jensen 2019-05-03 08:54:19 UTC
Created attachment 151150 [details]
Screendump from Calc 6.2
Comment 4 Xisco Faulí 2019-05-03 09:15:48 UTC
I can't reproduce it in

Versión: 6.2.3.2
Id. de compilación: aecc05fe267cc68dde00352a451aa867b3b546ac
Subprocs. CPU: 1; SO: Windows 6.1; Repres. IU: predet.; VCL: win; 
Configuración regional: es-ES (es_ES); Idioma de IU: es-ES
Calc: threaded

Could you please try to reproduce it with the latest version of LibreOffice
from https://www.libreoffice.org/download/libreoffice-fresh/ ?
I have set the bug's status to 'NEEDINFO'. Please change it back to
'UNCONFIRMED' if the bug is still present in the latest version.
Comment 5 Kim N. Jensen 2019-05-03 09:29:06 UTC
Still there in 

Version: 6.2.3.2 (x64)
Build ID: aecc05fe267cc68dde00352a451aa867b3b546ac
CPU tråde: 4; Styresystem: Windows 10.0; Gengiver af brugergrænseflade: Standard; VCL: win; 
Lokalisering: da-DK (da_DK); Sprog for brugergrænseflade: da-DK
Calc: threaded
Comment 6 Mike Kaganski 2019-05-03 09:42:36 UTC
Since https://git.libreoffice.org/core/+/e2e47898180e547cad7ccde1e5890385d573e551.

Eike: wouldn't just adding half of output resolution to the serial datetime be correct for proper rounding?
Comment 7 Xisco Faulí 2019-05-03 10:09:48 UTC
(In reply to Mike Kaganski from comment #6)
> Since
> https://git.libreoffice.org/core/+/e2e47898180e547cad7ccde1e5890385d573e551.

I should have read the description twice before I said I couldn't reproduce it, my bad.
i do confirm the commit mentioned above introduced this regression
Comment 8 Eike Rathke 2019-05-06 16:12:10 UTC
> Eike: wouldn't just adding half of output resolution to the serial datetime
> be correct for proper rounding?
No, that is more or less what those commits were about, rounding up a date+time or wall clock time into the next magnitude most times is wrong, especially if 23:59:59.9 would result in 00:00:00 then (together with date the next day).

The problem here is the underlying floating point value resulting from the subtraction (if the format is cleared (Ctrl+M) on A1:A2 you see the floating point values). Formatting with HH:MM:SS.000000 then shows the values 03:53:46.000002 and 03:23:59.999999 where the latter reveals the problem.

The correct way would be to format the result as duration, not wall clock time, i.e. [HH]:MM:SS displays the expected 03:24:00 on A2.

However, users don't use that.. not sure if we can do anything about it, maybe if for a formula the result format is automatically determined, but not if a wall clock time format is manually applied. Or maybe if we can detect a calculation result should fit into a certain wall clock time format.

Investigating.
Comment 9 Commit Notification 2019-05-07 23:06:38 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/85c0521f01f5c726e9f754b3175a550121e566c8%5E%21

Resolves: tdf#125099 round duration results in interpreter already

It will be available in 6.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 10 Eike Rathke 2019-05-07 23:13:52 UTC
Pending review
https://gerrit.libreoffice.org/71935 for 6-2
https://gerrit.libreoffice.org/71936 for 6-2-4
Comment 11 Commit Notification 2019-05-08 11:13:55 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/+/d27ad84ec7a0aafb07d6a6152c686f4bc802f661%5E%21

Fix typed flags bitmask, tdf#125099 follow-up

It will be available in 6.3.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 12 Commit Notification 2019-05-10 20:28:58 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-2":

https://git.libreoffice.org/core/+/f01e31762b02b8b896e726238eb2475c0e01ef82%5E%21

Resolves: tdf#125099 round duration results in interpreter already

It will be available in 6.2.5.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 Xisco Faulí 2020-09-06 12:03:52 UTC
Created attachment 165194 [details]
How it looks in LibreOffice 7.1 master
Comment 14 Xisco Faulí 2020-09-06 12:04:59 UTC
Hello Eike, Mike Kaganski,
In

Version: 7.1.0.0.alpha0+
Build ID: 5f7cbf60b71e78d4a980ec46953c4524ec0fd80c
CPU threads: 4; OS: Linux 5.7; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Calc: threaded

I still see 03:23:59. See screenshot. Should it be 3:24:00 instead ?
Comment 15 Albrecht Müller 2020-09-06 19:25:30 UTC
I think I have to live with this kind of problems as long as the relevant developers think they can use the concept of distinguishing between wall clock and duration time to fix some serious flaws is the specification of LibreOffice's date and time arithmetic. This idea may work for some special cases such as 

wall clock time - wall clock time = duration. 

But it will fail in cases like 

wall clock time + integer number * duration = wall clock time. 

The key point is that in this case the integer multiplier may increase some tiny round off error to such a degree that the underlying floating point arithmetic cannot correct this error any more. If the duration happens to be slightly too small then wall clock time rounding will round down which looks like an error in elementary integer arithmetic, see for example attachment 161500 [details] to bug 133389.

I see no way to work around this conceptual problem by using implementation tricks. That's why I created attachment 160356 [details] for bug 127170 where I tried to describe a way how a date and time arithmetic could work. I hope that this concept contains a rounding method that is so robust that an average user will probably never notice the effects of the underlying inexact floating point representation. There is also no need to distinguish between wall clock and duration time rounding. Thus legacy spreadsheets should work. So far I am not aware of any substantial feedback concerning this proposal.
Comment 16 Eike Rathke 2020-09-07 21:51:07 UTC
(In reply to Xisco Faulí from comment #14)
> I still see 03:23:59. See screenshot. Should it be 3:24:00 instead ?
You have to recalculate, i.e. on A2 hit F9.
Comment 17 Xisco Faulí 2020-09-08 08:18:58 UTC
(In reply to Eike Rathke from comment #16)
> (In reply to Xisco Faulí from comment #14)
> > I still see 03:23:59. See screenshot. Should it be 3:24:00 instead ?
> You have to recalculate, i.e. on A2 hit F9.

oh, I see, thanks a lot!
Comment 18 Commit Notification 2020-09-08 12:30:30 UTC
Xisco Fauli committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/e29b91e348be22037d9a3bcb3d6fe8ca91d5ee61

tdf#125099: sc_subsequent_filters_test: Add unittest

It will be available in 7.1.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 19 Albrecht Müller 2020-09-08 15:10:04 UTC
Created attachment 165278 [details]
Sample spreadsheet 1
Comment 20 Albrecht Müller 2020-09-08 15:11:05 UTC
Created attachment 165279 [details]
Screendump from Calc 6.0.4.2
Comment 21 Albrecht Müller 2020-09-08 15:12:07 UTC
Created attachment 165280 [details]
Screendump from Calc 6.4.5.2