Let's try to have a more formal approach<div><br></div><div>In serial date, d, the unit is a day</div><div>1 day = 24*60*60 = 86'400 seconds</div><div><br></div><div>then we can ask for</div><div><br></div><div>a) millisecond precision : 86'400'000 (nine digits precision)</div>
<div>b) microsecond precision : 86'400'000'000 (12 digits precision)</div><div>c) nanosecond precision : 86'400'000'000'000 (15 digits precision)</div><div> </div><div>Indeed many implementation of ISO 8601 date have only a) (millisecond precision) => this is the case for XML Schema Datatypes ( <a href="http://www.w3.org/TR/xmlschema-2/#isoformats">http://www.w3.org/TR/xmlschema-2/#isoformats</a> )</div>
<meta charset="utf-8"><div><br></div><div>Even for Oracle the default is b) and the MAXIMUM is c)</div><div><br></div><div>[[</div><div><h4 style="font-family: Arial, Helvetica, sans-serif; font-size: 10pt; margin-bottom: 1px; font-weight: bold; color: rgb(51, 102, 153); ">
TIMESTAMP Datatype</h4><span class="Apple-style-span" style="font-family: Arial, Helvetica, sans-serif; font-size: 13px; ">The TIMESTAMP datatype stores date and time including fractional seconds. Specify the TIMESTAMP datatype as follows:</span><blockquote style="font-family: Arial, Helvetica, sans-serif; font-size: 13px; ">
<code>TIMESTAMP [ (fractional_seconds_precision)]</code></blockquote><span class="Apple-style-span" style="font-family: Arial, Helvetica, sans-serif; font-size: 13px; ">where significant fractional_seconds_precision optionally specifies the number of digits in the fractional part of the SECOND datetime field and can be a number in the range 0 to 9. The default is 6. For example, you specify TIMESTAMP as a literal as follows:</span></div>
<div><font class="Apple-style-span" face="Arial, Helvetica, sans-serif">]]</font></div><div><font class="Apple-style-span" face="Arial, Helvetica, sans-serif"><br></font></div><div><font class="Apple-style-span" face="Arial, Helvetica, sans-serif"><br>
</font></div><div><font class="Apple-style-span" face="Arial, Helvetica, sans-serif"><br></font></div><div><font class="Apple-style-span" face="Arial, Helvetica, sans-serif">See some more comments below<br></font><br><div class="gmail_quote">
On Tue, Apr 20, 2010 at 8:33 PM, Chris Rae <span dir="ltr"><<a href="mailto:Chris.Rae@microsoft.com">Chris.Rae@microsoft.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div lang="EN-GB" link="blue" vlink="purple">
<div>
<p class="MsoNormal">Gareth and I have come up against a minor issue in the 8601 date work which we’d be interested in input from WG4 about. The question is about the number of decimal places of precision to mandate or allow in the 8601 lexical datetimes.</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">Back in Stockholm, we proposed mandating milliseconds. The problem with this is in the conversion to serial dates at runtime and back again. Using the function =NOW() in cell A1, for example, returns the value 40288.463572569446. This
represents 2010-04-20 at 11:07:32.67000014. Storing this with millisecond precision results in 2010-04-20 at 11:07:32.6700 – when loaded again at runtime, this represents the serial value 40288.4635725694<span style="background:yellow">39</span>.
This is not the same as the value we originally returned from the NOW() function. Of course, it’s pretty close. The net effect of using millisecond precision is that:</p></div></div></blockquote><div>Well here if I stick to millisecond then I get</div>
<div>11:07:32.670</div><div>which gives me</div><div><div>0.46357256944*4* (and not 4*39* as you get : rounding error ?)</div></div><div><br></div><div>which you can compare to</div><div>0.46357256944*6*</div><div><br></div>
<div>but please notice that the difference is very small : less than 134 nanoseconds (which make sense since the original value was 67000014)</div><div><br></div><div>So it advocates that millisecond precision is not that bad (in that case, because the use case is too gentle)</div>
<div><br></div><div>We should also keep in mind that IEEE 754 says that</div><div>* single precision floats (32 bits) are able to keep only 9 decimal (including the date part) which is very short</div><div>* double precision floats (64 bits) are able to keep 17 decimals (including date part)</div>
<div><br></div><div>Let compute what we need for the date part of serial date</div><div>365 = 1 years</div><div>36'525 = 1 century</div><div>730'500 = 2 millenia </div><div><br></div><div>It means even in double precisions we will only have 17-6 = 11 decimals left</div>
<div><br></div><div>If we compute more precisely we get</div><div><br></div><div> 2 millenia in milliseconds : 63'115'200'000'000 (14 digits)</div><div> 2 millenia in microseconds : 63'115'200'000'000'000 (17 digits)</div>
<div><br></div><div>(Wolfram Alpha says that 2 millenia in microseconds is 6.307 x 10^16 <a href="http://www.wolframalpha.com/input/?i=2+millenium+in+microseconds">http://www.wolframalpha.com/input/?i=2+millenium+in+microseconds</a> )</div>
<div> </div><div>It means that the range of 2 millenia at the microseconds precision in SERIAL date format could fit into a double precision decimal </div><div><br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div lang="EN-GB" link="blue" vlink="purple"><div>
<p class="MsoNormal"> </p>
<p><span style="font-family:Symbol"><span>·<span style="font:7.0pt "Times New Roman"">
</span></span></span>Files converted from transitional to strict will have very small value changes in datetimes and times</p>
<p><span style="font-family:Symbol"><span>·<span style="font:7.0pt "Times New Roman"">
</span></span></span>IS 29500 strict would not enable the storing of datetimes in cell values with the same precision it enabled at runtime</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">How many decimal places would we need in order for this to be the case? Well, we did some research here in Microsoft-land and it looks like:</p>
<p class="MsoNormal"> </p>
<p><span style="font-family:Symbol"><span>·<span style="font:7.0pt "Times New Roman"">
</span></span></span>To enable any datetimes after 1<sup>st</sup> January 1900 to round-trip without loss, we need
<b>11</b> decimal places on milliseconds</p></div></div></blockquote><div><br></div><div>Since 11 decimal could fit into double precision, I would go for 11</div><div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div lang="EN-GB" link="blue" vlink="purple"><div>
<p><span style="font-family:Symbol"><span>·<span style="font:7.0pt "Times New Roman"">
</span></span></span>To enable any pure times with no date component (i.e. values >0 and <1) to round-trip without loss, we need
<b>318</b> decimal places</p></div></div></blockquote><div>I have no clue of what this value means ???</div><div> </div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div lang="EN-GB" link="blue" vlink="purple"><div>
<p class="MsoNormal"> </p>
<p class="MsoNormal">Gareth did a lot of research into the number of milliseconds supported by various 8601 implementations and found out that the current highest is Oracle, which supports 9dps. We’re now stuck between accepting some miniscule precision loss
and mandating an unusual 8601 profiling which may be hard to implement.</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">I’m steering towards 11 dps (and just accepting some data loss on times) and Gareth is heading towards 9. We thought we’d put it to the SC34 test. Does anyone have any thoughts?</p>
<p class="MsoNormal"></p></div></div></blockquote><div><br></div><div>As I said, as long as it make sense to compute this in 64 bits precision, i'm fine</div><div><br></div><div>It means </div><div><br></div><div>Serial date should be stored in with 11 dps</div>
<div>ISO date should be stored with precision to the microseconds</div><div><br></div><div>Question : do we allow more precise implementations ?</div><div><br></div><div>Mohamed</div></div>-- <br>Innovimax SARL<br>Consulting, Training & XML Development<br>
9, impasse des Orteaux<br>75020 Paris<br>Tel : +33 9 52 475787<br>Fax : +33 1 4356 1746<br><a href="http://www.innovimax.fr">http://www.innovimax.fr</a><br>RCS Paris 488.018.631<br>SARL au capital de 10.000 €<br>
</div>