| |
 |
|
SQL Server
Tips by Burleson |
Keeping Time
SQL is the first programming language to have explicit temporal
datatypes. I have had the theory that if Cobol had been designed
with a TIMESTAMP datatype, we would have avoided all that Y2K
trouble. At least now, more people are aware of the ISO 8601 time
and date display standards. Who knows? Maybe people will start to
use them.
The temporal support in each SQL product can be classified as either
a "Unix-style" or "Cobol-style" internal representation.
In the Unix-style representation, each point in time is shown as a
very large integer number that represents the number of clock ticks
from a base date. This is how the Unix operating system handles its
temporal data. The use of clock ticks makes calculations very easy —
it becomes simple integer math. However, it is hard to convert the
clock ticks into a year-month-day-hour-minute-second format.
In the Cobol-style representation, the database has a separate
internal field for the year, month, day, hour, minute, and seconds.
This is great for displaying the information, but not for
calculations.
One of the debates in the SQL Standards Committee was how to handle
intervals of time. The reason that time is tricky is that it is
continuous. The defining mathematical property of a continuum is
that any part of it can be further sub-divided forever. Give me any
line segment and I can cut it into smaller segments endlessly. But
we run into the problem that the defining property of a point is
that it cannot be further subdivided. So how can there be points in
a continuum?
When you give a year, say 2000, you are really giving me an interval
of 365 days. Give me a date, say 2000-01-01, you are not giving me a
point; you are identifying an interval of 24 hours. Give me the date
and time 2000-01-01 00:00:00 and you are giving me an interval of 60
seconds. It never stops!!
The decision in SQL was to view time as a series of open ended
intervals. That is, the segment includes the starting point in time,
but never gets to the end point of the interval. This has some nice
properties. It prevents you from counting the end of one event and
the start of another event as identical moments in time. An open
interval minus an open interval gives open intervals as a result and
all points are accounted for.
This is a book excerpt from:
Advanced SQL Database Programmer Handbook
Donald K. Burleson, Joe Celko, John Paul Cook, Peter Gulutzan
ISBN: 0-9744355-2-X
http://www.rampant-books.com/ebook_dbazine_SQL_prog.htm
|
