Populate empty dates

Question

My database table structure is something like

stats_id stats_date   stats_visitors stats_visits
1        2015-08-01   10             20
2        2015-08-03   12             21
3        2015-08-04   14             24
4        2015-08-07   15             21

What is the easiest way to add empty stats date. For example I am missing 2015-08-02 so it should be:

stats_id stats_date   stats_visitors stats_visits
1        2015-08-01   10             20
5        2015-08-02   0              0
2        2015-08-03   12             21
3        2015-08-04   14             24
6        2015-08-05   0              0
7        2015-08-06   0              0
4        2015-08-07   15             21

I can check every day if it has data and populate but when I have over 10K rows its not really a good idea. If I use a check for each day I will generate over 10k queries.

Is there an easy way I can do this?

Is `stats_id` `AUTO_INCREMENT` ? – Praveen Aug 31 '15 at 07:13 — Praveen, Aug 31 '15 at 07:13
Yes @Praveen, its AUTO_INCREMENT – keepwalking Aug 31 '15 at 07:13 — keepwalking, Aug 31 '15 at 07:13

score 1 · Answer 1 · edited May 23 '17 at 12:14

This should help you out.

insert into Table1 select '',selected_date,'0','0' from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between (select min(stats_date) from Table1) and (select max(stats_date) from Table1) and selected_date not in (select stats_date from Table1);

The select adddate part of the query will give you all the dates in between the max and min date of your data set, and using not in we get only those dates which does not exist in your data set.

Since you have auto incrementing id field, you can pass an empty string in your select query and it will ignore this and insert the auto-incremented value.

Source of select all dates query: How to get list of dates between two dates in mysql select query

Hope this solves your problem.

score 0 · Answer 2 · answered Aug 31 '15 at 08:56

You can try this;

insert into tbl(stats_date, stats_visitors, stats_visits)
select in_date, 0, 0
from (
    SELECT 
        @date := DATE_ADD(@date,INTERVAL 1 DAY) AS in_date
    from
        (SELECT @date := min(stats_date) from tbl) r CROSS JOIN
        (SELECT 0  UNION ALL SELECT 1  UNION ALL SELECT 2 UNION ALL SELECT 3) a CROSS JOIN  
        (SELECT 0  UNION ALL SELECT 1  UNION ALL SELECT 2 UNION ALL SELECT 3) b CROSS JOIN 
        (SELECT 0  UNION ALL SELECT 1  UNION ALL SELECT 2 UNION ALL SELECT 3) c CROSS JOIN 
        (SELECT 0  UNION ALL SELECT 1  UNION ALL SELECT 2 UNION ALL SELECT 3) d CROSS JOIN 
        (SELECT 0  UNION ALL SELECT 1  UNION ALL SELECT 2 UNION ALL SELECT 3) e
    where @date < (select max(stats_date) from tbl)
) t
where in_date not in (select stats_date from tbl);

Here the sub-query will give dates between the min(stats_date) and max(stats_date).

Cross join will give maximum of 1024 rows( i.e. 4 power 5)
If your table have more then 1024 dates between min and max date, then add more cross join or more union all.

Populate empty dates

2 Answers2