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
Thanks! You saved me a fortune.
ReplyDelete2.2 Overlap so simple but hard to figure out on my own.
ReplyDeleteThank you!
I agree with above post. It was hard for me to even verify this simple query. Excellent method to find overlap. Thanks.
ReplyDeletewhat 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.
ReplyDeleteCan you tell me how to do this?
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.
DeleteFor 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.
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?
ReplyDeleteIf I understand you correctly, you can use the overlap query in a "WHERE ... NOT IN" clause:
DeleteSELECT * FROM rooms WHERE RoomID NOT IN (
SELECT RoomID FROM bookings WHERE @d2 > start_datetime AND end_datetime > @d1
)