[time-nuts] Precision Date/Time Calculations in MS SQL
tob at starhouse.org
Sat Jul 2 12:49:17 UTC 2011
I'm building, with help from fellow timenut Richard McCorkle, a distributed
cosmic-ray telescope to be run by students (elementary-college). Each of
the instruments has a muon detector, a GPS receiver (Navsync CW12-TIM), and
a timestamp generator designed by Richard, based on his PICTIC. The
instruments upload (to a central SQL database) an ASCII data string
consisting of date, time (to seconds), latitude, longitude, altitude, unit
ID number, and fractional seconds (nanoseconds). It's called the ERGO
Energetic Ray Global Observatory (www.symbiosis-foundation.org/cosmicray.htm).
We have about twenty units out in the field now and are aiming to top 100 by
the end of 2011.
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.
KE4SYS, oscillating between Miami and Cape Cod
More information about the time-nuts