Calc : (date)time parts, cell format and difference with other applications

[Winfried Donkers]

Hi all,

I added an attachment (https://bugs.documentfoundation.org/attachment.cgi?id=147433) to bug report tdf118800, with a comparison of the (date)time part functions (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND) and some (date)time cell formats.

I made this comparison before submitting a patch for bug report tdf121978 (https://bugs.documentfoundation.org/show_bug.cgi?id=121978) as I came across some unexpected differences between the time part functions and inconsistent (ambiguous?) differences between the definitions of date part functions in ODF1.2 part2.

In normal life, one could make a difference between (date)time and duration. Putting it simple, with (date)time a new minute only starts when 60 full seconds have passed since the last (i.e. the values are floored), but with duration a period can be rounded, e.g. an execution time of 1m 14.85s can be rounded to 1m 15s. This difference is not absolute (a quarter past twelve could be rounded and a contest to hold your breath at least 20 seconds probably will not accept 19.6s as adequate).
It seems ODF1.2 Part 2 uses both approaches. HOUR and MINUTE use floor and SECOND uses round (currently Calc uses floor, that's the subject of bug tdf121978).
But even with Calc's current SECOND function using floor, the cell format uses round for hour, minute and second presentation.
And weirdly a value of '2018-12-31 23:23:59.9' is shown as '2019-01-01 00' with cell format 'YYYY-MM-DD HH', but as '2018-12-31' with cell format 'YYYY-MM-DD'.

Excel seems quite consistent, by rounding all (date)time part functions and cell formats. It's just that with Excel new Year starts half a second sooner than in the real world. And the interoperability Calc-Excel is broken.

MariaDB and Transact SQL (used e.g. by Microsoft SQL server) are both consistent with their (date)time part functions: they all use floor. That  makes (date)time data-exchange easy.

And now the questions:
-What should Calc do? IMHO the current behaviour is inconsistent and needs attention, both the Calc functions and the cell formatting.
-Should we propose a change of ODF1.2 part 2 with respect to (date)time functions? And which change(s)?

Any answers?
(Note that if there are lots of opinions, I may participate little in any discussion at most. The final outcome will be my reference for further action on (date)time part functions and possibly cell formatting.)

Winfried

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice/attachments/20181212/47d6d8d4/attachment.html>