Visualizing Date Ranges

Thinking about date ranges and how to filter on them can be challenging. You might create a filter and realize you have some unexpected items in your results. Or maybe you're missing results you thought you should have. Either way, creating queries to filter on date ranges should be done very carefully, and this is how I like to visualize it. I hope you find this helpful as well.

Imagine you have a date range from RangeMin to RangeMax that you want to work with. We can visualize this with the following way.

Each horizontal line represents a record's StartDate and EndDate. All of these lines represent each different type of date range you will most-likely come across when writing queries. This is the base that I like to start off with.

Here's a list of items that you would probably come across, how it looks visually, and the filter expression that you could use to get the results you're looking for (written in JavaScript).

In each picture, the included results are highlighted in green, and the excluded results are grayed out.

I also made a small app in D3.js for this that you can interact with. You can check that out here and check out the source code here.

Starts Before RangeMin

This is a good example of if you were asked "hey, can you get me the projects that were started before X and have been completed?"

Expression

StartDate < RangeMin

Starts After RangeMin

Here's an example of "can you get me the projects that started after X?"

Expression

StartDate > RangeMin

Starts Before RangeMax

This is just like starting before RangeMin, except we're using RangeMax.

Expression

StartDate < RangeMax

Starts After RangeMax

This is just like starting after RangeMin, except we're using RangeMax.

Expression

StartDate > RangeMax

Ends Before RangeMin

Here's how the records would look if you filtered for those ending before RangeMin.

Expression

EndDate < RangeMin

Ends after RangeMin

Here's how it would look if you searched for records ending after RangeMin.

Expression

EndDate > RangeMin

Ends Before RangeMax

Same as ending before RangeMin, except using RangeMax.

Expression

EndDate < RangeMax

Ends After RangeMax

Just like ending after RangeMin, except using RangeMax.

Expression

EndDate > RangeMax

Starts or Ends Inside the Range

If you needed to get the records where they are "touching" the min and max of the range, and anything inside the range, here's how that would look.

Expression

(StartDate >= RangeMin && StartDate <= RangeMax) || (EndDate >= RangeMin && EndDate <= RangeMax)

Starts or Ends Outside the Range

This is where you want to filter out anything that was enclosed by the range.

Expression

StartDate < RangeMin || EndDate > RangeMax

Anything that is Inside the Range

Here's what it would look like if you wanted to get only those records which were enclosed inside the range.

Expression

StartDate >= RangeMin && EndDate <= RangeMax

Anything that is Outside the Range

Here's what it would look like if you wanted records that were not inside the range.

Expression

EndDate < RangeMin || StartDate > RangeMax

Anything that Involves the Range

If you needed to get everything that involved the range, but nothing more, this is how it would look.

Expression

StartDate < RangeMax && EndDate > RangeMin