Precision Date/Time Calculations in MS SQL
Gentlemen,
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.
Tom Bales
KE4SYS, oscillating between Miami and Cape Cod
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@blueyonder.co.uk
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:
-
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. -
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
Since we all know "time is money", this will also work. But it's
probably less efficient to store nanoseconds as if they were
cents so I hope no one actually uses that decimal encoding.
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
From: "Tom Van Baak" <tvb@...>
Sent: Saturday, July 02, 2011 2:44 PM
To: "Discussion of precise time and frequency measurement" <time-nuts@...>
Subject: Re: [time-nuts] Precision Date/Time Calculations in MS SQL
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
Since we all know "time is money", this will also work. But it's
probably less efficient to store nanoseconds as if they were
cents so I hope no one actually uses that decimal encoding.
.. although .. What was inefficient and to be avoided years back, may
still be inefficient, but may now run as fast as integers did back then.
Maybe decimal coding isn't such a bad solution? But I like your other
suggestions as well.
Cheers,
David
SatSignal software - quality software written to your requirements
Web: http://www.satsignal.eu
Email: david-taylor@blueyonder.co.uk
To bad you have already selected the database system. It is alway
best to think of requirements and then select parts the do what you
need. I would have selected a DMBS that allows the user to define
his own types and operators on those types. THen I'd define a type
called "Precision time" that works at what ever level I need, nano or
een Femtoseconds. You then define the greater then and less then
operators and soon andthen can use the type in SQL querries. THis
ability to define types is in many DBMSes certianly Ocrcle and
PostgreSQL. The later is free and open source. You might conceder
it. Postgres has a handy built-in type "Geo Location" and operators
for things like "distance" and "point inside bounding box" and many
others operators for using geographic points and regions.
Lacking a DBMS that allows you to extend the built-in types and
operators tou can simply store your data in multiple columns. Make a
table called "event_time" and make a column for julian day, seconds
and nanoseconds. Or if you absolutely need one field for date-time
then use a long ASCII string to record julian date with as many
decimal points as you need.
If you MUST squeeze store the date/time in a 64 bit float then use the
start of you project as the "epoc" Set it up so that Jan 1, 2011 is
"zero" and you will squeeze a few more decimal places into 64 bits.
My first choice would be to move to PostgreSQL. THen you can define
the classes and operators you needs and then you can take advance of
SQL to do must of you work. It's open source to the cost is nothing
Chris Albertson
Redondo Beach, California
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:
-
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. -
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@febo.com
To unsubscribe, go to
https://www.febo.com/cgi-bin/mailman/listinfo/time-nuts
and follow the instructions there.