CODEWITHSUNDEEP
mysqlsqlselect
chris_so
chris_so

Reputation: 882

Check if two date intervals intersect

I need to check in MySQL to see if two date intervals intersect. To explain better my issue: I have an event management module. If we have an event that was added like this:

start date : '2013-09-09 08:00:00'

end date : '2013-09-09 10:00:00'

and now I want to add another event that's like this:

case A:

start date : '2013-09-09 09:00:00'

end date : '2013-09-09 11:00:00'

OR like this :

case B:

start date : '2013-09-09 07:00:00'

end date : '2013-09-09 12:00:00'

I shouldn't be able to do this because an event was already added in that time interval (08-10)

For the first example (case A), I solved the issue by doing this :

SELECT * FROM `events` as e 
    where 
    '2013-09-09 08:00:00' between e.ev_date_start and e.ev_date_end -- date start
    OR
    '2013-09-09 11:00:00' between e.ev_date_start and e.ev_date_end -- date end

But for the second case (case B) I'm having trouble figuring it out...

Upvotes: 5

Views: 7515

Answers (5)

Vlado
Vlado

Reputation: 3777

Having in mind that you pre-guarantee (with some UI validation) that 2013-09-09 12:00:00 is bigger than 2013-09-09 07:00:00 you can simply cross the check this way:

WHERE 1
AND '2013-09-09 07:00:00' <= e.ev_date_end 
AND '2013-09-09 12:00:00' >= e.ev_date_start

This check is much simpler and faster. No need to mess up with OR statements or other complicated logic. Always have in mind that OR seriously harms the performance in SQL.

Upvotes: 1

quAnton
quAnton

Reputation: 786

A little example:

I want to find if these two mysql dates intersect with the dates on the database;

'2015-02-01 00:00:00', '2015-02-28 23:59:59'

The query to execute on phpMyAdmin or MySQL Workbench;

CREATE TEMPORARY TABLE `date_temp`
(
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `date_1` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Start date',
  `date_2` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Expiration date',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `date_temp` (`date_1`, `date_2`)
VALUES 
('2015-01-01 00:00:00', '2015-01-30 23:59:59'),
('2015-02-02 00:00:00', '2015-02-27 23:59:59'),
('2015-03-02 00:00:00', '2015-03-31 23:59:59'),
('2015-01-15 00:00:00', '2015-03-15 23:59:59'),
('2015-01-15 00:00:00', '2015-02-15 23:59:59'),
('2015-02-15 00:00:00', '2015-03-15 23:59:59');

SELECT * 
FROM `date_temp`
WHERE 
NOT 
(
    ('2015-02-01 00:00:00' > `date_2`) OR 
    (`date_1` > '2015-02-28 23:59:59') OR 
    (`date_2` < `date_1`) OR 
    ('2015-02-28 23:59:59' < '2015-02-01 00:00:00')
);

DROP TABLE `date_temp`;

The query creates a table, inserts some data and tests if the two dates intersects with the one present on the table. Then clears the table.

Upvotes: 0

sshilovsky
sshilovsky

Reputation: 998

The idea is to check if they DO NOT intersect, and then negate.

NOT ('2013-09-09 08:00:00' >= e.ev_date_end OR e.ev_date_start >= '2013-09-09 11:00:00')

which is logically equivalent to

'2013-09-09 08:00:00' < e.ev_date_end AND e.ev_date_start < '2013-09-09 11:00:00'

Upvotes: 8

Ravi K Thapliyal
Ravi K Thapliyal

Reputation: 51711

Test just that the start times of already assigned time blocks or the to be assigned time block do not fall between each other.

select * from `events` as e 
where '2013-09-09 08:00:00' between e.ev_date_start and e.ev_date_end
or e.ev_date_start between '2013-09-09 08:00:00'and '2013-09-09 11:00:00'

Upvotes: 5

Ben
Ben

Reputation: 52863

To cover them both you need 4 statements in your WHERE clause:

select *
  from `events` as e 
 where '2013-09-09 07:00:00' between e.ev_date_start and e.ev_date_end
    or '2013-09-09 12:00:00' between e.ev_date_start and e.ev_date_end
    or e.ev_date_start between '2013-09-09 07:00:00' and '2013-09-09 12:00:00'
    or e.ev_date_end between '2013-09-09 07:00:00' and '2013-09-09 12:00:00'

It might look simpler like this:

select *
  from `events` as e 
 where @start between e.ev_date_start and e.ev_date_end
    or @end between e.ev_date_start and e.ev_date_end
    or e.ev_date_start between @start and @end
    or e.ev_date_end between @start and @end

Upvotes: 5

Related Questions