Millisecond precision on 8601 dates

Tue Apr 20 21:52:36 CEST 2010

Let's try to have a more formal approach

In serial date, d, the unit is a day
1 day = 24*60*60 = 86'400 seconds

then we can ask for

a) millisecond precision : 86'400'000 (nine digits precision)
b) microsecond precision : 86'400'000'000 (12 digits precision)
c) nanosecond precision : 86'400'000'000'000 (15 digits precision)

Indeed many implementation of ISO 8601 date have only a) (millisecond
precision) => this is the case for XML Schema Datatypes (
http://www.w3.org/TR/xmlschema-2/#isoformats )

Even for Oracle the default is b) and the MAXIMUM is c)

[[
TIMESTAMP DatatypeThe TIMESTAMP datatype stores date and time including
fractional seconds. Specify the TIMESTAMP datatype as follows:

TIMESTAMP [ (fractional_seconds_precision)]

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:
]]

See some more comments below

On Tue, Apr 20, 2010 at 8:33 PM, Chris Rae <Chris.Rae at microsoft.com> wrote:

>  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:
>
Well here if I stick to millisecond then I get
11:07:32.670
which gives me
0.46357256944*4* (and not 4*39* as you get : rounding error ?)

which you can compare to
0.46357256944*6*

but please notice that the difference is very small : less than 134
nanoseconds (which make sense since the original value was 67000014)

So it advocates that millisecond precision is not that bad (in that case,
because the use case is too gentle)

We should also keep in mind that IEEE 754 says that
* single precision floats  (32 bits) are able to keep only 9 decimal
(including the date part) which is very short
* double precision floats (64 bits) are able to keep 17 decimals (including
date part)

Let compute what we need for the date part of serial date
365 = 1 years
36'525 = 1 century
730'500 = 2 millenia

It means even in double precisions we will only have 17-6 = 11 decimals left

If we compute more precisely we get

 2 millenia in milliseconds : 63'115'200'000'000 (14 digits)
 2 millenia in microseconds : 63'115'200'000'000'000 (17 digits)

(Wolfram Alpha says that 2 millenia in microseconds is 6.307 x 10^16
http://www.wolframalpha.com/input/?i=2+millenium+in+microseconds )

It means that the range of 2 millenia at the microseconds precision in
SERIAL date format could fit into a double precision decimal

>
> ·         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
>

Since 11 decimal could fit into double precision, I would go for 11

> ·         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
>
I have no clue of what this value means ???

>
>
> 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?
>
>
As I said, as long as it make sense to compute this in 64 bits precision,
i'm fine

It means

Serial date should be stored in with 11 dps
ISO date should be stored with precision to the microseconds

Question : do we allow more precise implementations ?

Mohamed
-- 
Innovimax SARL
Consulting, Training & XML Development
9, impasse des Orteaux
75020 Paris
Tel : +33 9 52 475787
Fax : +33 1 4356 1746
http://www.innovimax.fr
RCS Paris 488.018.631
SARL au capital de 10.000 €
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.vse.cz/pipermail/sc34wg4/attachments/20100420/27806dde/attachment-0001.htm>