[time-nuts] Precision Date/Time Calculations in MS SQL

[Tom Van Baak]

Tom,

I've run into similar problems with time-stamps, in general.
Here's how to think about it.

A nanosecond is 9 decimal places, a second within a day is
another 5 decimal places. A day within a couple of centuries
takes another 5. So you are already at the precision of a
18-digit (53-bit) double precision floating point number (and
that's not even counting the issues with representing fractions
in binary floating point).

One easy but ugly solution is to use a larger floating point
data type (like 128-bits). A cleaner solution is to use 64-bit
integers and record everything in multiples of nanoseconds.

But I actually do neither. Instead I find it convenient to never
combine really long-term units like years and days with
short-term units like seconds and nanoseconds. Examples:

1) take your ascii string and record the date/time part to 1
second accuracy and record the nanosecond fraction as
an integer (0 to 999,999,999). You can use a 32-bit integer
for this.

2) take your ascii string and record the date/time part to 1
day (e.g., MJD) and record the seconds and nanoseconds
part as a double precision floating point fraction of days, or
as double precision floating point (integer) nanoseconds per
day (0 to 86,399,999,999,999).

You could come up with other solutions, but the key is that
combining very long time scales with very short time scales
as a single number requires a large number of bits. Best to
split it into two halves as in the examples above.

/tvb

> Here's the rub:  when I use Microsoft SQL to convert an ASCII string to
> "datetime", it does so only to a precision of a few microseconds (at least,
> that's what it seems like to me).  So, when I add in the fractional seconds,
> the overall precision isn't good enough (it's off by MICROseconds, for
> crying out loud!).  I'm trying for 10-nanosecond precision in the system, so
> that's just NG.  I'm sure the physicists who do real VLBI stuff brew up
> their own code to handle their data, but we're trying to do it with
> commercially-available database servers.
> 
> I figured there might be a timenut out there who happens to know the ins and
> outs of SQL (there's an odd combination of skills for you), who could help
> out.  Any ideas are appreciated.
> 
> Tom Bales
> KE4SYS, oscillating between Miami and Cape Cod