SQL & how different databases implement it

Some vaguely structured thoughts on the differences that you see between SQL as implemented by one DBMS and that of another.

08 December 2005


Interval between two points in time

How long ago did you start school? How long is a train journey scheduled to take? How long did the policyholder hold their life policy before dying?

Many times developers are required to calculate an interval between one point in time and another point in time. Often this is between now and some specified, fixed past date. For example if I tell you my date of birth you can calculate how old I am. This is an interval of time although you may express this as a number of years or months or days, usually depending on how old I am (my baby is 4 months old, not 0 years old) or how accurate you need to be, or how pedantic.

Microsoft SQL Server and Sybase use the DATEPART function to return the number of "somethings" between two dates (and remember that a date in this context is the date and time combined - an instant in time). A "something" can be months, days or years, etc, as specified by the developer. The answer that you are given is an integer. So for example if you were calculating my age you could either come up with 38 years or 13982 days or some other somewhat higher numbers depending on when you are reading this post.

Oracle, by contrast, has implemented two data types specifically to hold intervals of time. One of them, their imaginatively named "INTERVAL DAY TO SECOND" data type, is used to hold the period between two points in time to an accuracy of seconds. If you calculated my age it would not be an integer, but an interval of time to the nearest second, which you might express in writing as something like "13982 days, 14 hours, 5 minutes and 17 seconds". But equally you could write it down as "1208095517 seconds" - it is exactly the same thing, just presented in a different way.

Oracle's idea is that you should be able to calculate and store intervals of time "raw" and then just convert them into an appropriate character string (like the two examples above) to display to a human user.

It is then a breeze to perform date arithmetic - subtraction and addition - in order to answer questions like: given the start and end dates of a journey made yesterday, when would that journey finish if it were to start in 4 hours time and take 10% longer.

Being able to calculate and store in tables intervals of time is a far better and more powerful way of handling date arthimetic than being restricted to constantly converting intervals of time into integers and having to do your own mathematics.

Comments: Post a Comment

Subscribe to Post Comments [Atom]

<< Home



September 2005   October 2005   December 2005   January 2006   March 2006   May 2006   June 2006   September 2006   May 2007   June 2007   November 2007   December 2007   March 2008   April 2008   December 2008   January 2011  

This page is powered by Blogger. Isn't yours?

Subscribe to Posts [Atom]