Millisecond precision on 8601 dates

[Horton, Gareth]

Hi all

The millisecond example should be : 2010-04-20 at 11:07:32.670

I believe Chris means fractional seconds, not milliseconds in the bullet point starting with "To enable any datetimes after 1st January 1900 ..."

Also it should be fractional seconds here too: "Gareth did a lot of research into the number of milliseconds ..."

Gareth

From: Chris Rae [mailto:Chris.Rae at microsoft.com]
Sent: 20 April 2010 19:33
To: SC 34 WG4
Cc: Horton, Gareth
Subject: Millisecond precision on 8601 dates

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.

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.463572569439. 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:


*         Files converted from transitional to strict will have very small value changes in datetimes and times

*         IS 29500 strict would not enable the storing of datetimes in cell values with the same precision it enabled at runtime

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:


*         To enable any datetimes after 1st January 1900 to round-trip without loss, we need 11 decimal places on milliseconds

*         To enable any pure times with no date component (i.e. values >0 and <1) to round-trip without loss, we need 318 decimal places

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.

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?

Chris
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.vse.cz/pipermail/sc34wg4/attachments/20100420/b0e333a7/attachment.htm>