Bug 118800 - HOUR, MINUTE and SECOND function return value that is 1 too high, when seconds value of time >= 59.5 (HOUR, MINUTE) or >= xx.5 (SECOND)
Summary: HOUR, MINUTE and SECOND function return value that is 1 too high, when secon...
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium minor
Assignee: Winfried Donkers
URL:
Whiteboard: target:6.2.0 target:6.1.0.2 target:6....
Keywords:
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
 
Reported: 2018-07-17 13:19 UTC by Dmitro
Modified: 2021-07-01 09:36 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
SECOND(...) return second-1. (56.37 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-12-07 16:11 UTC, gmolleda
Details
datetime parts comparison (20.84 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-12-11 10:13 UTC, Winfried Donkers
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Dmitro 2018-07-17 13:19:58 UTC
Description:
Issue is reproduced for all input types.

Steps to Reproduce:
1.Open LibreOffice Calc
2.Enter following formulas to any cell
=HOUR(-0.000001)

Actual Results:
24


Reproducible: Always


User Profile Reset: Yes


OpenGL enabled: Yes

Additional Info:
Version: 6.0.6.1 (x64)
Build ID: dc68ad6c5cde3c62874b96422f2e5e8252499bad
CPU threads: 4; OS: Windows 10.0; UI render: default; 
Locale: en-US (en_US); Calc: group
Comment 1 raal 2018-07-17 20:29:59 UTC
definition 6.10.10 HOUR
Summary: Extracts the hour (0 through 23) from a time.
Syntax: HOUR( TimeParam T )
Returns: Number
Constraints: None
Semantics: Extract from T the hour value, 0 through 23, as per a 24-hour clock. This is equal to:
DayFraction=(T-INT(T))
Hour=INT(DayFraction*24)

confirm with Version: 6.2.0.0.alpha0+
Build ID: d11313f341d1d9a9c73c736932441a89d5292e37
CPU threads: 4; OS: Linux 4.4; UI render: default; VCL: gtk3;
Comment 2 Winfried Donkers 2018-07-18 17:44:15 UTC
There is a rounding error in the code.
For example, =HOUR("10:59:59.6") returns 11, whereas it should return 10.

I will fix it.
Comment 3 Winfried Donkers 2018-07-18 17:49:34 UTC
Likewise, =MINUTE("10:20:59.6") returns 21 instead of 20, and 
=SECOND("10:30:59.6") return 0 instead of 59.
Comment 4 Xisco Faulí 2018-07-18 18:31:29 UTC
Also reproduced in

LibreOffice 3.3.0 
OOO330m19 (Build:6)
tag libreoffice-3.3.0.4
Comment 5 Commit Notification 2018-07-19 12:02:44 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c69e7266916ac1b8917477fb4eccdb9098da5792

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

It will be available in 6.2.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 6 Commit Notification 2018-07-19 17:28:34 UTC
Winfried Donkers committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=bda9288ffee552b55eed9dbf02e1204957bd4513&h=libreoffice-6-1

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

It will be available in 6.1.0.2.

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

Affected users are encouraged to test the fix and report feedback.
Comment 7 Commit Notification 2018-08-27 22:16:34 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=273b3e10eab70ebc084cb62568bd699fddfb376e

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

It will be available in 6.2.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 8 Commit Notification 2018-08-28 10:50:24 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=98cb91686901dc0133c5c23dc5658d9623dbd436

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

It will be available in 6.2.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 9 Commit Notification 2018-09-04 08:01:38 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=184aee6abd202f6f4e2c18d21f96a0d1a9fd947f&h=libreoffice-6-1

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

It will be available in 6.1.2.

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

Affected users are encouraged to test the fix and report feedback.
Comment 10 Commit Notification 2018-09-06 15:52:09 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-6-1-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=34887e7a8d36603ade1a0621d511906b826d0298&h=libreoffice-6-1-1

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

It will be available in 6.1.1.

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

Affected users are encouraged to test the fix and report feedback.
Comment 11 gmolleda 2018-12-07 16:11:55 UTC
Created attachment 147357 [details]
SECOND(...) return second-1.

SECOND with images in LO 6.1.3.2 and 6.0.6.2
Comment 12 gmolleda 2018-12-07 20:18:03 UTC
Now if you write

A2: 00:03:09
A3: 2018-10-22 11:31:44
A4: 2018-10-22 11:34:53
A5 : =A4-A3 --> result 00:03:09

The number in A2 is 0,0021875 and in A5 is 0,002187499995.

I think that the error is in calculator with float numbers.

In Excel 2016 is the same behaviour and function SECOND works as the old function SECOND in LO.
Comment 13 gmolleda 2018-12-08 07:52:43 UTC
As I do not know if it is a matter of modifying SECOND or touching on the subtraction of dates/hours, I have created a BUG with the problem: https://bugs.documentfoundation.org/show_bug.cgi?id=121978
Comment 14 Winfried Donkers 2018-12-11 10:13:34 UTC
Created attachment 147433 [details]
datetime parts comparison

Document compares results for YEAR/MONTH/DAY/HOUR/MINUT/SECOND functions and cell formats for a (date)time with fractional part of the second value > 0.5.
Comparison is for ODF1.2 Part2, Calc 6.1.3, Calc version with which document is opened, Excel 2016, MariadDB, Transact SQL.

It shows where there are differences and may help to make changes to ODF1.2 Part 2 and/or Calc.
Comment 15 gmolleda 2018-12-22 05:31:26 UTC
The correct behavior was the previous, rounding.
If someone wants the exact second without rounding you can use the functions:
10:50:59,99	
0	=SECOND(A1)
59	=VALUE(MID(TEXT(A1;"hh:mm:ss,0000");7;2))

With LibreOffice 6.0.7.3
Comment 16 Mike Kaganski 2021-07-01 09:36:59 UTC
See also https://issues.oasis-open.org/browse/OFFICE-4094