LibreOffice Calc SECOND function returning wrong value - why?

Question

I have a table with a timestamp column (column A) that contains the timestamp with millisecond resolution.

Example:

I have separate columns for extracting different time 'components' (year, month, date, etc.), but the seconds column (SS) is giving the wrong value for the rows highlighted in red.

The formula for column SS is simply: =SECOND([timestamp_column]), for example in row 2, it is =SECOND(A2)

Why is it giving the wrong value for some rows?

Answer 1

It dawned on me upon looking closely that for all rows where the SECOND function returns the wrong answer, they are all the correct SECOND value +1. Looking at the milliseconds in the source column, I realized that they are all 500ms or more 'past the second'...therefore, the SECOND function must be ROUNDING.

The ONLY indication of this is nothing more than implied here: enter link description here

where is says:

That is the only use of the word "ROUND" on the entire page.

The formula to workaround this behaviour (which is not a bug, it is just obtuse) is:

=SECOND(INT(A2*86400)/86400)

which truncates the milliseconds off the source, or

=TEXT(SECOND(INT(A2*86400)/86400),"00")

if you want it formatted as a double digit.

The table now correctly extracts the 'CURRENT SECOND' of the timestamp in question, instead of the 'NEAREST SECOND':

Answer 2

You'll notice that all the "wrong" values of seconds actually come from a value with a fractional element that is greater than (or possibly greater than or equal to) 0.5. For example, 2.577, 5.752, 8.754, and so on.

Hence it's almost certainly rounding these numbers to the nearest integer.

From some documentation found online, it states the formula used is SECOND = ROUND(MinuteFraction * 60) so I would assume, if you need it truncated rather than rounded, you could use the formula:

=TRUNC(MinuteFraction * 60)

To calcucate MinuteFraction (as per that linked page):

DayFraction = DateTime – INT(DateTime)
HourFraction = (DayFraction * 24) - INT(DayFraction * 24)
MinuteFraction = (HourFraction * 60) - INT(HourFraction * 60)