
Type: Improvement

Status: Open

Priority: Major

Resolution: Unresolved

Affects Version/s: ODF 1.2

Fix Version/s: ODF 1.4

Component/s: None

Labels:None

Environment:
OpenFormula
Email from Mike Kaganski, 15 September 2020 reads:
As mentioned in [1], there is a problem in the OASIS OpenFormula standard (as of v.1.3 [2]). The spreadsheet functions YEAR, MONTH, DAY, HOUR, MINUTE are defined in such a way that for any given value of time, they discard fractions of respective time part: e.g., MINUTE would only return whole number of minutes contained in the parameter, discarding any fraction of a minute, no matter how close it is to 1. On the other hand, spreadsheet function SECOND is defined to round the number of seconds in minute fraction, thus it may return a number that is greater by 1 compared to the whole number of seconds actually contained in the minute fraction.
Furthermore, SECOND is defined to return 0 through 59 (never 60), but to use formula equivalent to ROUND(MinuteFraction * 60). Here is the first minor inconsistency: ROUND(MinuteFraction * 60) results in numbers 0 through 60, not through 59.
It is impossible to modify the definition of SECOND to return 0 through 60, because it would then become inconsistent with other spreadsheet software, introducing major interoperability problems. Correcting the equivalent formula to result in 0 through 59, like "MOD(ROUND(MinuteFraction * 60), 60)", brings us to the next inconsistency:
ÂÂÂ For all values of TimeParam T which have (MinuteFraction * 60) between 59.5 and 60.0, the set of functions:
Â ÂÂ ÂÂ =YEAR(T) &""& MONTH(T) &""& DAY(T) &" "& HOUR(T) &":"& MINUTE(T) &":"& SECOND(T)
ÂÂÂ results in an ISO datetime string that is off by ~1 minute (actually, exactly by value of (MinuteFraction * 60) seconds).
This happens because value returned by SECOND will be 0 (zero), as if the next minute has already started, while all other functions return values of the minute, hour, day, ... that are still active for T.
For instance, for T corresponding for 20200915 15:23:59.60, the result of the mentioned formula would be "2020915 15:23:0".
It is impossible to change definition of SECOND to discard fraction of a second (for consistency with other functions), because then it would again be inconsistent with other spreadsheet software. So the only solution is to redefine the other functions to be consistent with (improved) SECOND definition, like this:
ÂÂÂ * Proposed improvement for SECOND(TimeParam T): Second=MOD(ROUND(T*86400);60)
ÂÂÂ * Proposed change for MINUTE(TimeParam T): Minute=(MOD(ROUND(T*86400);3600)SECOND(T))/60
ÂÂÂ ... etc.
This would not only make the functions consistent within the standard, but also with other spreadsheet software.
[1] https://bugs.documentfoundation.org/show_bug.cgi?id=136615#c0