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@febo.com To unsubscribe, go to https://www.febo.com/cgi-bin/mailman/listinfo/time-nuts and follow the instructions there.