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

[Chris Albertson]

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