Intervals Part 1 - Definitions and Terms

Introduction

Intervals are used frequently in SQL Server databases. Despite that, there is little consistency in how intervals are handled.  This partly derives from the fact that there is no native SQL data type for intervals.  The purpose of this article is to introduce basic terms and definitions to SQL Server DBAs and developers, so that we can start to develop a more consistent approach to handling intervals.

What are Intervals?

Intervals are representations of a range of data.  In fact, the common term for an interval is "range".  Quite often, these ranges represent periods of time with a begin date and an end date.  For instance, a student database will have an interval representing the period between when the student first matriculated and when they graduated.  During this period, they are a student, and after this period, they are an alum. It is important that intervals have a well-defined order.  You cannot graduate from a school before you have become a student at that school.

In databases, intervals are usually represented with two fields: one representing the beginning of the range, and the other representing the end of the range.

Examples of Intervals

Here are some examples of common intervals:

• Age Ranges: A recent report I was looking at used the following intervals for ages: 16-19, 20-24, 25-34, 35-44, 45-54, 55-64, and 65+.
• Temperature Ranges: The weather is reported with actual or expected highs and lows, and these form an interval.  For example, the current forecast for Philadelphia gives the following intervals: Fri 43-46; Sat 45-57; Sun 45-59; Mon 34-55; Tue 37-59; and Wed 45-61.  (All temperatures are in degrees Fahrenheit.)
• Snow/Rain Fall:  Weather reports will also include intervals for expected snow or rain fall.  2"-4" in Philadelphia, 5"-9" in Reading.
• Salary Ranges: Many jobs will list a salary range: $65,000-$85,000 or $100,000-$120,000.
• Names: When you register for an event, you may see that the registration table has multiple lines based on the first letter of the last name to help speed the registration process:  A-D, E-J, K-N, O-S, T-Z

Perhaps the most common types of intervals are datetime intervals.  Many reports that include datetime data will use some sort of datetime interval to group records.  For example, hourly, daily, weekly, monthly, quarterly, and yearly are all examples of common datetime intervals.

Types of Intervals

There are three types of intervals based on the inclusion of the endpoints.

1. A Closed interval includes both endpoints.  For example, saying that the week runs from Sunday through Saturday is an example of a closed interval, because the week includes both Sunday and Saturday.  The SQL BETWEEN keyword uses a closed interval.
2. An Open interval excludes both endpoints.
3. A Half-Closed (or Half-Open) interval includes one endpoint and excludes the other.  This is a hybrid of the previous two interval types.  For example, a day runs from midnight to midnight, but only the first midnight is included in the day.  The second midnight is the start of the following day.

Some people distinguish the types of intervals by specifying for each endpoint whether it is included.  The different types would then be closed-closed, closed-open, open-closed, and open-open.  Since datetime intervals are one of the most common, and they are almost exclusively closed-open, there isn't generally a need to distinguish between the two types of half-closed intervals.  For that reason, I tend to prefer the shorter descriptions.  The reason that datetime intervals are closed-open, is that midnight is usually the endpoint, because it is easy to specify, and it is included at the beginning of the range, but often excluded from the end of the range.

One of the common issues when working with intervals is confusing closed intervals and half-closed intervals, particularly when working with DATETIME data.  The reason for this is that we tend to think in terms of full days, but when SQL translates these dates to datetime data, it converts it to midnight.  For that reason, you need to make sure that if you are converting between date and datetime that you are using the correct interval type.

Other Terminology

There are certain other terms that may be used when talking about intervals.

1. An invalid interval has a begin point that is greater than the end point.
2. A degenerate interval has the same value for both endpoints.  A closed degenerate interval contains a single value and an open degenerate interval is empty. A half-closed degenerate interval is invalid, because the same point cannot be both included and excluded from the interval.

Interval Relationships

One of the most common problems with intervals is how they are related to other intervals. For example, if you're scheduling a room, you need to know if anyone else has that room reserved during that time period.  Another example is when you are looking for a job, you want to know if your desired salary matches the range of salary for the job(s) you are applying for.

James F. Allen¹ introduced terminology for specific types of interval relations. [1]

Note that each relationship has a corresponding relationship where the two objects are switched.  So "precedes" is matched with "preceded by".  "equals" matches itself.  Except for "contains"/"during", the rest of the matches have one relationship expressed in the active voice and the other expressed in the passive voice.

Part of the reason that there are so many questions about intervals is that there isn't a broadly used framework to discuss intervals within the SQL community.  The purpose of this article is to introduce such a framework to make it easier to talk about and work with intervals.

Footnotes

[1]  To the best of my knowledge, James F. Allen and I are not related.

References

[1] Allen's Interval Algebra https://www.ics.uci.edu/~alspaugh/cls/shr/allen.html