[Chartjs]-Select missing dates from a table in MySQL

by

1👍

Check this:

select this_date,given_dates from
(SELECT ADDDATE('2015-09-01', INTERVAL @i:=@i+1 DAY) AS this_date
        FROM (
        SELECT a.a
        FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
        ) a
        JOIN (SELECT @i := -1) r1
        WHERE 
        @i < DATEDIFF('2018-01-30', '2015-09-01')) as a

left JOIN

(select given_dates from
 (select '2015-09-01' as given_dates union all 
select  '2015-09-03' union all 
select  '2015-09-05' union all 
select  '2015-09-06' union all 
select  '2015-09-07') as a) as b
on a.this_date = b.given_dates

sample Result:

2015-09-01  2015-09-01  
2015-09-02      
2015-09-03  2015-09-03  
2015-09-04      
2015-09-05  2015-09-05  
2015-09-06  2015-09-06  
2015-09-07  2015-09-07  
2015-09-08      
2015-09-09      
2015-09-10      
2015-09-11

You can get easily the null value of it.

Leave a comment