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
