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

Bob Camp lists at rtty.us
Sat Jul 2 17:42:51 UTC 2011


Hi

A relatively simple solution is to use the datetime field to do what it was 
originally supposed to do. Let it tag time to a second. Dump in your 
nanoseconds as a double indicating time since the start of the second. If at 
some point you need more precision, you have the room for it (1x10^-16 
seconds).

Keeping the datetime lets you keep all the sorting stuff that goes with it. 
If you want the data from last May, it knows what May is. Much easier to 
find stuff when it's in easy to understand units.

Bob

-----Original Message----- 
From: Tom Van Baak
Sent: Saturday, July 02, 2011 9:41 AM
To: Discussion of precise time and frequency measurement
Subject: Re: [time-nuts] Precision Date/Time Calculations in MS SQL

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


_______________________________________________
time-nuts mailing list -- time-nuts at febo.com
To unsubscribe, go to 
https://www.febo.com/cgi-bin/mailman/listinfo/time-nuts
and follow the instructions there. 





More information about the time-nuts mailing list