Why are the NOW() and SECOND() functions not showing the same value?

[scooterbird]

I have a spreadsheet in LO Calc (under Win 10) which shows the current time using the NOW() function, formatted as HH:MM:SS. I also use the spreadsheet to calculate times in other time zones, which I format using, among other functions, TEXT(SECOND(NOW()),"00"). These numbers frequently do not match when I press F9 to recalculate. When I was using 6.1.x.x, the SECOND was often one second behind NOW; I’ve recently upgraded to 6.3.6.2 and now SECOND is often one second ahead of NOW. A bit of experimentation shows that they are off from each other by maybe 0.3-0.5 seconds.

There’s not an absolute need that I have these exactly synchronized, but it is cosmetically irritating. Can anyone shed some light as to why this is happening, and how I might fix it?

Many thanks in advance.

[mikekaganski]

Maybe there’s some lengthy calculations happening between the two cells? And maybe it makes sense to use reference to the cell with the Now() from the formula with SECOND instead of calling Now() again?

(I’d think that to calculate times in different time zones, it is better to add/subtract hours to the original time in the cell with Now(), and use the same HH:MM:SS cell formatting, not convert to text using SECONDS and TEXT.)

[JohnSUN]

You yourself could understand the reason if you formatted the result NOW() in hundredths of a second (HH:MM:SS.00). This is not a mistake, this is the usual rounding.

Just use TEXT(NOW();"SS") instead your TEXT(SECOND(NOW());"00")

[Lupp]

Quoting from what was claimed in the explanations to the question: "When I was using 6.1.x.x, the SECOND was often one second behind NOW; "
I did not yet test, but don’t believe this. Sorry! - And apologies! See amendment.

Quoting again: “… and now {using V 6.3.6.2} SECOND is often one second ahead of NOW.”
That’s the expected behavior.

The respective specifications (OASIS ODF) weren’t changed for a long time.

Reasons in short:

Standard time-formats always round down.
(I didn’t research if this is explicitly specified. It’s fact, however, and it’s reasonable regarding how clocks work.)

The extracting functions HOUR() and MINUTE() also round down their interim results still containing a fraction.
The extracting function SECOND() rounds the “common” way.

Remember the spreadsheet principle: Something always is inconsistent in any context.

===Edit 2020-06-17 about 21:25 UTC===
Sorry. I was wrong.
Meanwhile I tested with about a dozen versions from LibO 3.3. til 7.0 (beta1) and with AOO 4.1.7.
The results were awefully confusing. I would assume, the state reached with V 6.2.5 will now persist.

For your own tests you may use the attached example.
ask250932roundingByTimeFormatsTimeextractingFunctions.ods

[erAck]

And we have to keep on living with those inconsistencies because back in time Microsoft didn’t have the guts to eliminate such nonsense brought over from Lotus 1-2-3 to Excel. So now even more users still expect it.

[Lupp]

@erAck: I anyway suspected this kind of history.
It’s just a funny fact that “heritage till AOO 4.1.7” produce the same strange effect to show the seconds rounded up if a time is formatted “…SS” as specified for the SECOND() function, which is against the “do-it-as-a-clock-would-rule”.
The first change made about V6.1 then did not fix this problem, but changed the fuction with the effect that it no longer regarded its specification.
With 6.2 this bad change was reverterd with the erffect that SECOND() now again regarded the (bad due to history) specification, and the formatted time shows “rounded down to full unit”.
It’s really a mess, and clearing this cost me about an hour. Wasted time again!"
What about this idea: Every standard function has in parallel a version THISFUNCTION.REASONABLE() doing its task like a sound specification should define it, disregarding otherwise implemented compatibility with nonsense?