Comparing date ranges

by

This is a classical problem, and it’s actually easier if you reverse the logic.

Let me give you an example.

I’ll post one period of time here, and all the different variations of other periods that overlap in some way.

           |-------------------|          compare to this one
               |---------|                contained within
           |----------|                   contained within, equal start
                   |-----------|          contained within, equal end
           |-------------------|          contained within, equal start+end
     |------------|                       not fully contained, overlaps start
                   |---------------|      not fully contained, overlaps end
     |-------------------------|          overlaps start, bigger
           |-----------------------|      overlaps end, bigger
     |------------------------------|     overlaps entire period

on the other hand, let me post all those that doesn’t overlap:

           |-------------------|          compare to this one
     |---|                                ends before
                                 |---|    starts after

So if you simple reduce the comparison to:

starts after end
ends before start

then you’ll find all those that doesn’t overlap, and then you’ll find all the non-matching periods.

For your final NOT IN LIST example, you can see that it matches those two rules.

You will need to decide wether the following periods are IN or OUTSIDE your ranges:

           |-------------|
   |-------|                       equal end with start of comparison period
                         |-----|   equal start with end of comparison period

If your table has columns called range_end and range_start, here’s some simple SQL to retrieve all the matching rows:

SELECT *
FROM periods
WHERE NOT (range_start > @check_period_end
           OR range_end < @check_period_start)

Note the NOT in there. Since the two simple rules finds all the non-matching rows, a simple NOT will reverse it to say: if it’s not one of the non-matching rows, it has to be one of the matching ones.

Applying simple reversal logic here to get rid of the NOT and you’ll end up with:

SELECT *
FROM periods
WHERE range_start <= @check_period_end
      AND range_end >= @check_period_start

More Related Contents:

  1. Group by month and year in MySQL
  2. Hours and seconds are lost when storing a date in the database [closed]
  3. You can’t specify target table for update in FROM clause
  4. How to populate a table with a range of dates?
  5. MySQL Query GROUP BY day / month / year
  6. generate days from date range
  7. SQLite INSERT – ON DUPLICATE KEY UPDATE (UPSERT)
  8. MIN/MAX vs ORDER BY and LIMIT
  9. How can I merge two MySQL tables?
  10. Can table columns with a Foreign Key be NULL?
  11. Mysql recursion?
  12. is there a group_concat function in ms-access?
  13. Is there an alternative to TOP in MySQL?
  14. How many rows in a database are TOO MANY?
  15. Why does this SQL code give error 1066 (Not unique table/alias: ‘user’)?
  16. Find closest date in SQL Server
  17. Is it possible to have an indexed view in MySQL?
  18. mysql – how many columns is too many?
  19. How to calculate the number of “Tuesdays” between two dates in TSQL?
  20. Best database field type for a URL
  21. Use A Union Or A Join – What Is Faster [closed]
  22. MySQL – how to front pad zip code with “0”?
  23. Find the number of columns in a table
  24. Mysql, reshape data from long / tall to wide
  25. MySQL: Compare differences between two tables
  26. Mysql strip time component from datetime
  27. How do I start auto increment from a specific point?
  28. Date serial in SQL?
  29. In SQL how do I get the maximum value for an integer?
  30. Get the first and last date of next month in MySQL

Leave a Comment