June 6, 2012

SQL Query to Find Overlapping (Conflicting) Date Ranges

Date ranges are often stored in a database table as a pair of datetime columns; for example, start_date and end_date. A common operation on date ranges is to find those that lie outside or overlap a given date range. A hotel reservation system for example might need to check if a room is available from Jun/1/2012 5:00 PM to Jun/3/2012 11:00 AM before confirming. Writing a query for this is much easier than you expect.

Date Range Query for Exclusive End Dates

-- 1.1) select date ranges that lie outside [d1, d2) (d2 and end_date are exclusive)
SELECT * FROM <table> WHERE @d2 <= start_date OR  end_date <= @d1

-- 1.2) select date ranges that overlap [d1, d2) (d2 and end_date are exclusive)
SELECT * FROM <table> WHERE @d2 >  start_date AND end_date >  @d1

Date Range Query for Inclusive End Dates

-- 2.1) select date ranges that lie outside [d1, d2] (d2 and end_date are inclusive)
SELECT * FROM <table> WHERE @d2 <  start_date OR  end_date <  @d1

-- 2.2) select date ranges that overlap [d1, d2] (d2 and end_date are inclusive)
SELECT * FROM <table> WHERE @d2 >= start_date AND end_date >= @d1

The above queries are based on Allen's Interval Algebra. The following ASCII art should explain the first query:

       @d1 |--------| @d2       
           .        . |--------| @d2 < start_date
           .        |--------|   @d2 = start_date
|--------| .        .            end_date < @d1
  |--------|        .            end_date = @d1
    |--------|      .            
           .      |--------|     
           |--------|            
           . |----| .            
         |------------|          

Example Queries

The following SQL script should help you test the queries and their results:

1) Example Data and Query for Exclusive End Dates
CREATE TABLE table1 (
  start_datetime DATETIME NOT NULL,
  end_datetime DATETIME NOT NULL,
  comments VARCHAR(100) NOT NULL,
  KEY (start_datetime),
  KEY (end_datetime)
);

INSERT INTO table1 (start_datetime, end_datetime, comments) VALUES
('2012-05-01 00:00:00', '2012-06-01 00:00:00', 'May 01 2012 - May 31 2012'),
('2012-05-01 00:00:00', '2012-06-16 00:00:00', 'May 01 2012 - Jun 15 2012'),
('2012-05-01 00:00:00', '2012-08-01 00:00:00', 'May 01 2012 - Jul 31 2012'),
('2012-06-10 00:00:00', '2012-06-20 00:00:00', 'Jun 10 2012 - Jun 19 2012'),
('2012-06-16 00:00:00', '2012-08-01 00:00:00', 'Jun 16 2012 - Jul 31 2012'),
('2012-07-01 00:00:00', '2012-08-01 00:00:00', 'Jul 01 2012 - Jul 31 2012');

SET @d1 = '2012-06-01 00:00:00'; -- Jun 01 2012
SET @d2 = '2012-07-01 00:00:00'; -- Jun 30 2012
1.1) Outside Query
SELECT * FROM table1 WHERE @d2 <= start_datetime OR end_datetime <= @d1;

-- start_datetime         end_datetime           comments
-- -------------------    -------------------    -------------------------
-- 2012-05-01 00:00:00    2012-06-01 00:00:00    May 01 2012 - May 31 2012
-- 2012-07-01 00:00:00    2012-08-01 00:00:00    Jul 01 2012 - Jul 31 2012
1.2) Overlap Query
SELECT * FROM table1 WHERE @d2 > start_datetime AND end_datetime > @d1;

-- start_datetime         end_datetime           comments
-- -------------------    -------------------    -------------------------
-- 2012-05-01 00:00:00    2012-06-16 00:00:00    May 01 2012 - Jun 15 2012
-- 2012-05-01 00:00:00    2012-08-01 00:00:00    May 01 2012 - Jul 31 2012
-- 2012-06-10 00:00:00    2012-06-20 00:00:00    Jun 10 2012 - Jun 19 2012
-- 2012-06-16 00:00:00    2012-08-01 00:00:00    Jun 16 2012 - Jul 31 2012
2) Example Data and Query for Inclusive End Dates
CREATE TABLE table2 (
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  comments VARCHAR(100) NOT NULL,
  KEY (start_date),
  KEY (end_date)
);

INSERT INTO table2 (start_date, end_date, comments) VALUES
('2012-05-01', '2012-05-31', 'May 01 2012 - May 31 2012'),
('2012-05-01', '2012-06-15', 'May 01 2012 - Jun 15 2012'),
('2012-05-01', '2012-07-31', 'May 01 2012 - Jul 31 2012'),
('2012-06-10', '2012-06-19', 'Jun 10 2012 - Jun 19 2012'),
('2012-06-16', '2012-07-31', 'Jun 16 2012 - Jul 31 2012'),
('2012-07-01', '2012-07-31', 'Jul 01 2012 - Jul 31 2012');

SET @d1 = '2012-06-01'; -- Jun 01 2012
SET @d2 = '2012-06-30'; -- Jun 30 2012
2.1) Outside Query
SELECT * FROM table2 WHERE @d2 < start_date OR end_date < @d1;

-- start_date    end_date      comments
-- ----------    ----------    -------------------------
-- 2012-05-01    2012-05-31    May 01 2012 - May 31 2012
-- 2012-07-01    2012-07-31    Jul 01 2012 - Jul 31 2012
2.2) Overlap Query
SELECT * FROM table2 WHERE @d2 >= start_date AND end_date >= @d1;

-- start_date    end_date      comments
-- ----------    ----------    -------------------------
-- 2012-05-01    2012-06-15    May 01 2012 - Jun 15 2012
-- 2012-05-01    2012-07-31    May 01 2012 - Jul 31 2012
-- 2012-06-10    2012-06-19    Jun 10 2012 - Jun 19 2012
-- 2012-06-16    2012-07-31    Jun 16 2012 - Jul 31 2012

7 comments:

  1. Thanks! You saved me a fortune.

    ReplyDelete
  2. 2.2 Overlap so simple but hard to figure out on my own.

    Thank you!

    ReplyDelete
  3. I agree with above post. It was hard for me to even verify this simple query. Excellent method to find overlap. Thanks.

    ReplyDelete
  4. what does d2 and d1 actually mean? I am really new at this and a little confused. I have a table that has 3 coloumns - id, start date and end date. The ids have multiple entries with different start and end dates and i need to find those ids that have the dates that overlap.
    Can you tell me how to do this?

    ReplyDelete
    Replies
    1. The @d1 and @d2 are the start and end dates that you want to check. So, if you want to check if May/1/2013 - May/5/2013 conflicts with the dates in the database table you would replace @d1 with '2013-05-01' and @d2 with '2013-05-05' in the examples; or treat @d1/@d2 as user variables or as parameters for prepared statements.

      For second part of your question, you can simply inner join the table to itself to find the conflicts within rows of that table:

      SELECT
      main_table.id, main_table.start_datetime, main_table.end_datetime,
      temp_table.id, temp_table.start_datetime, temp_table.end_datetime
      FROM table1 AS main_table
      INNER JOIN table1 AS temp_table ON (
      main_table.id <> temp_table.id AND
      temp_table.end_datetime > main_table.start_datetime AND
      main_table.end_datetime > temp_table.start_datetime
      )

      Note that this query could send your database server into a deep coma if there are few thousand rows.

      Delete
  5. I have a table with 4 columns - Name, startdate, enddate and comment - consider it to be a booking "calendar" (entries indicate "busy" time). Having 2 dates (@d1 and @d2) I want to find out which names that does not have bookings (free) in my specified date range. Can you tell me how to get this information?

    ReplyDelete
    Replies
    1. If I understand you correctly, you can use the overlap query in a "WHERE ... NOT IN" clause:

      SELECT * FROM rooms WHERE RoomID NOT IN (
      SELECT RoomID FROM bookings WHERE @d2 > start_datetime AND end_datetime > @d1
      )

      Delete

Comment moderation is enabled. Approved comments are usually published (or answered) within 48 hours.
Note: Click the "load more..." button if necessary to see all comments.