Mysql Find missing date ranges

Question

I have a table with date ranges:

    create table d (
    date_start date,
    date_end date
    );

    insert into d values('2014-03-05', '2014-04-10');
    insert into d values('2014-05-01', '2014-06-05');
    insert into d values('2014-07-10', '2014-08-15');

I want to complete this table with missing date ranges in 2014. In this case that would be:

    2014-01-01 - 2014-03-04
    2014-04-11 - 2014-04-30
    2014-06-06 - 2014-07-09
    2014-08-16 - 2014-12-31

Any mysql query suggestions?

Edit

Better use these values:

    create table d (
        date_start date,
        date_end date
        );

        insert into d values('2014-06-01', '2014-06-30');
        insert into d values('2014-07-01', '2014-08-03');
        insert into d values('2014-09-01', '2014-09-30');

Edit 2

I am almost there with this:

    SELECT
            date_start,
            date_end

    FROM

    (SELECT 
      ends.point AS date_start,
      starts.point AS date_end
    FROM 
      (SELECT 
        d.date_end+INTERVAL 1 DAY AS point, 
        @n:=@n+1 AS num 
      FROM 
        d 
          CROSS JOIN (SELECT @n:=1) AS init0
          ORDER BY date_start
      ) AS ends 
      INNER JOIN 
      (SELECT 
        d.date_start-INTERVAL 1 DAY AS point, 
        @m:=@m+1 AS num 
      FROM 
        d 
          CROSS JOIN (SELECT @m:=0) AS init1
          ORDER BY date_start
      ) AS starts 
      ON ends.num=starts.num
    UNION ALL 
      SELECT 
      '2014-01-01', 
        MIN(date_start) - INTERVAL 1 DAY 
      FROM 
        d 
      WHERE 
        date_start>='2014-01-01'
    UNION ALL 
      SELECT 
        MAX(date_end)+INTERVAL 1 DAY, 
        '2014-12-31'
      FROM
        d 
      WHERE 
        date_end <= '2014-12-31'
    ) as dates
    WHERE
      date_start < date_end
    ORDER BY 
      date_start;

However, this query returns wrong results for the following intervals set:

    create table d (date_start date, date_end date);

    insert into d values('2014-01-01', '2014-01-09');
    insert into d values('2014-01-10', '2014-03-15');
    insert into d values('2014-03-16', '2014-04-20');
    insert into d values('2014-05-01', '2014-07-30');
    insert into d values('2014-08-01', '2014-09-30');
    insert into d values('2014-12-25', '2014-12-31');

It seems it cannot handle single days like 2014-07-31 - 2014-07-31.

Sorry, the intervals may be consecutive, e.g. 2014-06-01 - 2014-06-30; 2014-07-01 - 2014-07-31. But they never overlap. — Victor, May 30 '14 at 11:16

Alma Do · Accepted Answer · 2014-05-30T17:41:53.713

5

In case if your date intervals will not be nested or intersecting, you can use trick with JOIN to produce result set. So, to select desired record set, you'll need:

SELECT
  *
FROM
(SELECT 
  ends.point AS date_start, 
  starts.point AS date_end 
FROM 
  (SELECT 
    d.date_end+INTERVAL 1 DAY AS point, 
    @n:=@n+1 AS num 
  FROM 
    d 
      CROSS JOIN (SELECT @n:=1) AS init0
  ) AS ends 
  INNER JOIN 
  (SELECT 
    d.date_start-INTERVAL 1 DAY AS point, 
    @m:=@m+1 AS num 
  FROM 
    d 
      CROSS JOIN (SELECT @m:=0) AS init1
  ) AS starts 
  ON ends.num=starts.num 
UNION ALL 
  SELECT 
    '2014-01-01', 
    MIN(date_start) - INTERVAL 1 DAY AS date_end
  FROM 
    d 
  HAVING 
    date_end>'2014-01-01' 
UNION ALL 
  SELECT 
    MAX(date_end)+INTERVAL 1 DAY AS date_start, 
    '2014-12-31' 
  FROM
    d 
  HAVING 
    date_start<'2014-12-31' 
) as dates
WHERE
  date_start<=date_end
ORDER BY 
  date_start;

that will result in

+------------+------------+
| date_start | date_end   |
+------------+------------+
| 2014-01-01 | 2014-03-04 |
| 2014-04-11 | 2014-04-30 |
| 2014-06-06 | 2014-07-09 |
| 2014-08-16 | 2014-12-31 |
+------------+------------+

(fiddle for this is here)

To "complete" your table you may use INSERT..SELECT syntax with SELECT query above.

edited May 30 '14 at 17:41

answered May 30 '14 at 08:13

Alma Do

37,009
9
76
105

This works almost fine, but gives wrong result e.g. 2014-07-01 - 2014-06-30 when there are consecutive periods, say 2014-06-01 - 2014-06-30; 2014-07-01 - 2014-07-31. Gave an example of set of periods in my original post edit. – Victor May 30 '14 at 11:09
@Victor you've said they won't intersect (and yes, consecutive periods case _is an intersection_). Anyway, I've updated (fiddle link too). It's not too hard to handle this case of intersection – Alma Do May 30 '14 at 11:10
Then I was wrong perhaps. Yes, they won't overlap, but may be consecutive. – Victor May 30 '14 at 11:14
I see this really did the trick! Thanks a lot! By the way, this was needed for a property rental engine with seasonal prices (see original question [link](http://stackoverflow.com/questions/23927260/mysql-seasonal-pricing-for-apartment-booking) ). – Victor May 30 '14 at 11:33
Found that it cannot handle single days like 2014-07-31 - 2014-07-31. Updated my post with your slightly updated code as I have it now. When there is a period of just a single day, it shows incorrect results. – Victor May 30 '14 at 16:59
Well.. it's even not correct interval. Anyway, I've updated (using `HAVING` to exclude such case) – Alma Do May 30 '14 at 17:06
Did not work for me, however I changed "WHERE date_start – Victor May 30 '14 at 17:23
No, this should work well. Your case with consecutive intervals is [here](http://www.sqlfiddle.com/#!2/761c96/5) - it seems, you've not tried updated query (I've did edits in post itself) – Alma Do May 30 '14 at 17:24
I understand that technically 1 day is not an interval, but the task is to fill the whole year, no gaps) – Victor May 30 '14 at 17:26
I can see the updated code and fiddle, but it shows only two rows for me, missing 2014-07-31 - 2014-07-31. – Victor May 30 '14 at 17:31
Oh, finally got what is wrong. Just [change `<` to `<=`](http://www.sqlfiddle.com/#!2/761c96/6). It's incorrect technically, but will do the trick. – Alma Do May 30 '14 at 17:33
That's it! Thank you, I am still testing different scenarios and will let you know if anything goes wrong. – Victor May 30 '14 at 17:41

Mysql Find missing date ranges

1 Answers1

Linked