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

[David J Taylor]

> 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

Tom,

Some top-of-the-head thoughts.

Typically, dates and times are represented internally as 64-bit floating 
point numbers, which given the base of around 1900 results in a precision 
of around a microsecond (very roughly).  Perhaps hence the results you are 
seeing.  You may be better off using a UNIX-style format with 32-bits for 
integer seconds (since 1-Jan-1970) and then a 32-bit fraction of a second. 
Something like nanosecond resolution, if my sums are right.  It will 
likely mean writing your own routines for date to number conversion, and 
vice-versa.  This format is used within NTP, so some conversion routines 
may already be available.

Another suggestion is to keep the time in string format, to whatever 
precision you need.  Whilst fine for storing the data, this will be 
considerably less efficient if calculations are required on the data, of 
course!

I don't know how many digits are allowed for the "money" type!  <G>
  http://msdn.microsoft.com/en-us/library/ms187752.aspx
  http://msdn.microsoft.com/en-us/library/ms190476.aspx

Hope that helps a bit.

Cheers,
David
-- 
SatSignal software - quality software written to your requirements
Web:  http://www.satsignal.eu
Email:  david-taylor at blueyonder.co.uk