Not Implementing Intervals

TLDNR: Standard SQL Intervals are broken so I’ve implemented DBDateRepeat instead. Date subtraction has been replaced with secondsFrom(otherDate) and getDateRepeatFrom(otherDate).

Intervals are included in the SQL standard as representations of time durations. That is they are meant to represent the difference between a day and a date. A day is the concept of 24 contiguous hours while a date is a particular 24 hours such as 1/March/2013.

Unfortunately the industry and formal standard are both broken.

The formal standard is to store either year-months intervals or day-time intervals, whereas the industrial standard is split: either lots more interval types are supported or intervals don’t exist at all. MySQL even manages to include INTERVAL while not supporting intervals at all.

The fundamental problem seems to be caused by 2 issues: a belief that dates are simple, and February 29th. These two issues are unresolvable, yet both SQL92 and industry implementations try to resolve them.

The first issue leads to the belief that intervals can be represented by an exact number of seconds. SQL92’s day-time interval is implicitly this type of implementation. The day part of the day-time can take any integer value while the hours, minutes, and seconds parts are constrained to fit within the next higher unit. 12 days 23 hours 59 minutes 59 seconds is just a complicated way of saying 12.9999884259259 days. If that were the solution to the problem of representing time intervals we’d just store a large decimal of seconds and forget about it.

Unfortunately the 29th of February breaks the simple time idea. How many seconds are there between the 28th of February and the 1st of March? You can’t answer that question until you know the year.

The SQL92 standard avoids the question by creating a break between day-time intervals and year-month intervals. Unfortunately this solution just complicates the situation as we now have 2 mutually incompatible intervals.

A better solution is to not treat date differences as a number of seconds, days, or months but as a collection of all the date parts. This is what I have implemented in DBDateRepeat.

The concept is that DateRepeats are specified as collections of the differences between all the individual parts of the dates. No normalising of the values occurs and any date part can have any value, positive or negative. This means a duration can span February 29th successfully and avoid the problem:

(1/Mar/2013 - 28/Feb/2013) = (1/Mar/2012 - 28/Feb/2012) = (+1 months -27 days)

As the name implies DBDateRepeat is most useful for handling repetitions of events, usually in conjunction with a start date. If you have an event on the first of every month a DBDateRepeat of +1 months will help you find the correct date, similarly a DateRepeat of +14 days will work for fortnightly events. More complex DateRepeats are also possible: +1 years +2 days +5 hours.

However this is not the end of difficulties with dates. Rather it is just one solution to the conundrum of how to handle our weird time system. If a marathon runner jogs from 28th February 2013 to 1 March 2013 he doesn’t want his time to be +1 month -27 days, instead he wants to know the exact number of seconds he took. So the high precision option is still available as DateExpression.secondsFrom(otherDate).