<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.E-mailStijl17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="NL" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span lang="EN-GB">Hi all,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">I added an attachment (<a href="https://bugs.documentfoundation.org/attachment.cgi?id=147433">https://bugs.documentfoundation.org/attachment.cgi?id=147433</a>) 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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">I made this comparison before submitting a patch for bug report tdf121978 (<a href="https://bugs.documentfoundation.org/show_bug.cgi?id=121978">https://bugs.documentfoundation.org/show_bug.cgi?id=121978</a>) 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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">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).<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">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).<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">But even with Calc's current SECOND function using floor, the cell format uses round for hour, minute and second presentation.
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">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'.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">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.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">And now the questions:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">-What should Calc do? IMHO the current behaviour is inconsistent and needs attention, both the Calc functions and the cell formatting.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">-Should we propose a change of ODF1.2 part 2 with respect to (date)time functions? And which change(s)?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">Any answers?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB">(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.)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="mso-fareast-language:NL">Winfried<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-GB"><o:p> </o:p></span></p>
</div>
</body>
</html>