One simple pleasure of the New Year celebration was that we gained a second. Wonderful, we thought, though nobody in the media tried to explain why. (It was the insertion of a “leap second” in order to maintain synchronization with sidereal time)
Just when one thinks that one has a grip on the science of time and calendars, along comes something that gives pause for thought. Dates, times, durations, calendars, intervals, events and so on are intrinsically a tricky subject. When database applications get involved in any sort of work that involves times, dates, intervals, durations, events and so on, then chaos often ensues. When anyone is bold enough to try to clarify the subject, then discussion always seems to get lively. There seems to be little consensus on how we should handle, analyse and store a fundamental measure of our lives. It is all more complicated than it seems, and mistakes are inevitable.
The debate about time and dates once reached fever pitch at the time as the millennium approached, when many computer systems had to be patched to correctly calculate time intervals based on the difference in dates in different millennia. You would have thought that, after all that focus on Date/time calculation, there was little more to say. Unfortunately, this isn’t the case. There is a great deal we ought to be doing to assist those who are faced with the development of reliable time-based applications.
To store a time interval in SQL Server, you are obliged to store the start date/time and end date/time , and hope to goodness that the two dates are the same time zone and legal date (e.g. daylight saving). We had hopes for SQL Server 2008. At least it provides a date/Time with the UTC (GMT) component. It also allows you to store dates before January 1, 1753. However, It still does nothing to implement the SQL 2003 INTERVAL Datatype, or the operators and functions to calculate time/date-based information, such as OVERLAPS. There is going to be quite a lot of work to allow SQL Server to conform to the ISO/IEC 9075 SQL2008 standard.
