Bug 127476 - Rounding behaviour of YEAR and SECOND function is inconsistent
Summary: Rounding behaviour of YEAR and SECOND function is inconsistent
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2019-09-10 13:47 UTC by Albrecht Müller
Modified: 2020-11-18 22:04 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:
Regression By:

Example file to show inconsistent rounding (9.04 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-04-21 15:03 UTC, Albrecht Müller
xls version of the example file (7.00 KB, application/x-ole-storage)
2020-04-21 15:04 UTC, Albrecht Müller
A workaround for consistent rounding (20.88 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-05-20 19:39 UTC, Albrecht Müller

Note You need to log in before you can comment on or make changes to this bug.
Description Albrecht Müller 2019-09-10 13:47:48 UTC
Description: The results of the YEAR and the SECOND function may be inconsistent. The reason is probably that the functions use different rounding strategies. 

How to reproduce: Open some spreadsheet and enter „=SECOND("1999-12-31 23:59:59,9")“ and „=YEAR("1999-12-31 23:59:59,9")“ in two different cells. 
Actual behaviour: You will get 0 seconds and year 1999. This seems to imply that the SECOND function rounds to the next second, while the YEAR function rounds down.

Expected behaviour: A consistent behaviour would be to either get 59 seconds and year 1999 (round down) or 0 seconds and year 2000 (round to the next second). The latter variant probably is the way how spreadsheet applications usually work.
Comment 1 m.a.riosv 2019-09-11 11:55:53 UTC
Please attach a sample file.
BTW can you test with a newest version. 6.0 it's EOL, not new updates on it.
Comment 2 Albrecht Müller 2019-09-11 19:15:08 UTC
I think it is not useful to upload a sample file for the following reasons:

1) It is pretty easy to see the effect. Just enter the formulas into some cells of a spreadsheet and check what the results are. Maybe you have to set an option such that Calc accepts time values that are given as strings.

2) Uploading a file may distract from the real problem I want to address with this report. Note that I made this bug report to depend on bug 127477 to point out that you cannot fix this bug before this other bug has been fixed.

Some background information: I have a Ubuntu version available and discovered that I do not want to upgrade to this version as date & time arithmetic has changed and that about 50% of the results of trivial date & time calculations are now wrong. For details see bug 127334.

So I learned that it is the specified behaviour of date & time functions like HOUR, MINUTE or SECOND to deliver pseudo random values that are correct in 50% of the cases and one off in the other 50%. Of course, the help information does not rule out this behaviour. Some other specification seems to exist that has exactly this behaviour as a consequence. So a change in behaviour I consider a critical bug has been classified as NOTABUG.

Thus the real problem is that as a user I cannot know what these date & time functions are supposed to do. That's the reason for bug 127477. I think there is a lot of confusion about how the date & time arithmetic is intended to work which results in a couple of bug reports and fixes. This is the reason why I just uploaded an additional comment to bug 127477 that tries to clarify what I see a common source of confusion and unnecessary work.
Comment 3 Buovjaga 2020-04-19 15:41:03 UTC
Uploading an example document will not distract from anything. It is a basic requirement.

The documentation requested in bug 127477 was implemented last year.

Change back to UNCONFIRMED after you have provided the document.
Comment 4 Albrecht Müller 2020-04-21 15:03:39 UTC
Created attachment 159789 [details]
Example file to show inconsistent rounding
Comment 5 Albrecht Müller 2020-04-21 15:04:43 UTC
Created attachment 159790 [details]
xls version of the example file
Comment 6 Albrecht Müller 2020-04-21 15:09:37 UTC
I uploaded two sample files: an .odt and an .xls version. No matter of if I open the .odt file with the an old Windows version of LibreOffice or a Linux version: both show 0 seconds and year 1999. I used the .xls version to open it with a Excel 97 version which is more than 20 years old. Excel showed the same values as LibreOffice but when I reentered the formula that calculates the year the value changed from 1999 to 2000. So I assume that the initial values are just those stored in the .xls file and the value 2000 comes from Excel’s own calculation.

My explanation of this behaviour: More than 20 years ago Microsoft engineers were aware of the fact that when rounding 59.9 seconds to 60 and then mapping this to 0 seconds a carry should go to the minutes value and this carry may ripple down to the years position. LibreOffice seems to use no carry in this case. Consequence: The two programs disagree into which millenium the result should go.
Comment 7 Albrecht Müller 2020-05-20 19:39:37 UTC
Created attachment 161049 [details]
A workaround for consistent rounding

This spreadsheet is intended demonstrate the effects of the workaround described in bug 127170 comment 18 on bug 127476
Comment 8 b. 2020-05-26 12:20:59 UTC
repro, year: 1999, second: 0, 

(the millenium problem doesn't apply, as well 1999-12-31 as 2000-01-01 belong to 20!, 21'th started at 2001-01-01, the error of celebrating the next millennium 12 months too early is somewhat bigger than the rounding errors in calc ...)
Comment 9 Eike Rathke 2020-05-26 16:19:51 UTC
Only SECOND() is specified to round, MINUTE() and HOUR() are specified to not round. Yes rounding SECOND() is odd, complains go to Excel.
Specifically you do not want to see 2000-01-01T00:00:00 if the value actually is 1999-12-31T23:59:59.9 ... and yes, obtaining 1999-12-31T23:59:00 for individual HOUR():MINUTE():SECOND() functions is not much better, not rounding at all would be expected, but again, complain with Excel.


Btw, calculating with strings is luck and depends on the locale if it contains separators. If at all then use a date+time string in real ISO 8601 format which recognizes both

Closing as WontFix.
Comment 10 Albrecht Müller 2020-05-26 22:44:09 UTC
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 [details] (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.
Comment 11 b. 2020-05-27 15:45:20 UTC
sorry, i'm not convinced ... 

there are two to three overall targets: 

- excel conmpatibility, 

- obeying standards, 

just installed ex$el 2010 winx64 for a countercheck, results: year 2000, second 0, (month 1, day 1, hour 0, minute 0), 

calc: year 1999, second 0, (month 12, day 31, hour 23, minute 59), 

if calcs results are required by a standard ... 

- change that standard, or 

- produce results which are not! 59,9 seconds off, 

(as @Eike said: 'Yes rounding SECOND() is odd, complains go to Excel', but then either avoid ex$el behaviour, or copy it in full, not copy the odd thing reg. 'compatibility' and leave out the corrections necessary to deal with the crap)  

if it's not possible to meet both targets (compatibility and standard) something must decide ... imho 'usability', a program should work in a way and produce results which are correct for and can be handeled by 'normal' users, without exotic unversity grade to be happy about their special fp-knowledge. for those users it's 'not so easy' to accept arbitrarely choosen different handling of similar things, neither to understand the resulting calculation errors.  

either 'wall clock standard', a wall clock does not! show '00' for the seconds at 59:59,9 minutes, it shows 59 seconds till the minute is full (as it shows 59 for the minute till the hour is full), OR! intelligent rounding, but no mix-mess. 

(a friend of mine often quoted: 'either consquent or inconsequent, but not this or that as you like!')

the 'wall clock standard' is especially made for those stupid users who'd have problems with rounded minutes? then we ought to give them 'wall clock standard' for seconds too. and of course for milliseconds, microseconds, nanoseconds and so on. 

how do caesium clocks tick in their last digit? rounding? 

or! we have to stick to compatibility, and round up. 

excels results are kohärent within themself? and users can calculate with them, 

former behaviour of calc was consistent in itself? and users could calculate with the results? 

and now we have something that fits to a 'standard', but breaks calculations in a spreadsheet and nags users ... ??? that's not real progress. 

rounding in stepped 'place value' systems with circulr iteration of the digits is 'not so easy', you have to care for the carry, but 'the standard' is to take the unrounded basic value, e.g. wednesday for '=weekday(43831,6;1)'? or 1 as a answer to 'give me the tens digit of 315,6 please'? 

if anyone (calc) deviates from that standard it should have good reasons, and results consistent within themself. ex$el deviates in a consistent way, calc is indifferent and deviating.