mysql间隔日期,显示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

解决方案

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 |

+------------+------------+------+