CODEWITHSUNDEEP
mysqlsql
Panos Kalatzantonakis
Panos Kalatzantonakis

Reputation: 12683

Show gaps between dates in MySQL

How can I show the remaining/complementary dates in mysql?

For example in my table that has 2 columns, a snapshot for
from_date>14-06-2014 and to_date<01-07-2014

would give this output:

From date   || To_date
15-06-2014  || 20-06-2014
23-06-2014  || 27-06-2014 
29-06-2014  || 30-06-2014

I would like to be able to show the dates that we have a gap and no records exist, like this:

2 //21-06-2014 - 23-06-2014
1 //28-06-2014

Is this possible? Thank you

Upvotes: 5

Views: 1375

Answers (1)

Alex Monthy
Alex Monthy

Reputation: 1877

along those lines:

drop table if exists dates;
create table dates (d_from date, d_to date);
insert into dates values 
('2014-06-15'  , '2014-06-20'),
('2014-06-23'  , '2014-06-27' ),
('2014-06-29'  , '2014-06-30' );

select low.d_to, high.d_from, to_days(high.d_from) - to_days(low.d_to) - 1 as gap

from dates low, dates high
where high.d_from = (select min(d_from) from dates where d_from > low.d_to)
;

Which means: join the table to itself on adjacent end/start dates and compute the difference.

+------------+------------+------+
| d_to       | d_from     | gap  |
+------------+------------+------+
| 2014-06-20 | 2014-06-23 |    2 |
| 2014-06-27 | 2014-06-29 |    1 |
+------------+------------+------+

Upvotes: 6

Related Questions