MySQL - fill missing dates

Question

I have this query and I want to fill missing dates with some values (zero for example...)

SELECT date, SUM(val1) as sum_val  
FROM table1  
WHERE date BETWEEN '2016-03-04' AND '2016-03-10'  
GROUP BY date

Here is result:

|--------------------------  
|date, sum_val  
|--------------------------  
|2016-03-04, 21568  
|--------------------------  
|2016-03-05, 14789  
|--------------------------  
|2016-03-08, 20568  
|--------------------------  
|2016-03-10, 5841  
|--------------------------

How can I populate missing dates with zero values? Does anyone has an idea?

I need this data for chart preview.

you need to use an additional table with calendar or an procedure to make temporary table with dates range — splash58, Apr 04 '16 at 12:01
just loop for one month and make condition if its match with ur exist date then no need to insert data and rather then add with 0. — RJParikh, Apr 04 '16 at 12:05
You can select each date manualy, other then that with pure sql - no — sagi, Apr 04 '16 at 12:05
you need only sql query or php code also acceptable for insert zero? — RJParikh, Apr 04 '16 at 12:08
@ Ruchish Parikh I try to find the best way to do that... no matter if I use mysql or php. — Heril Muratovic, Apr 04 '16 at 12:10

androidnation · Answer 1 · 2016-04-04T12:25:02.377

4

You can try this PHP loop:

$date1 = Your_first_date_value;
$date2 = Your_limit_date;
for ($d1 = $date1; d1<=$date2; strtotime("+1 day", strtotime($d1))) {
    //Fill in values
}

strtotime("+1 day", strtotime($date1)) will increment by 1 day the current value of $date1 until it is equal to $date2.

When a date does not have a value it will be zero, and if the date exist, it will get the sum according to MySQL result.

edited Apr 04 '16 at 12:25

answered Apr 04 '16 at 12:15

androidnation

626
1
7
19

@downvoter why ????? It is working for me in my project – androidnation Apr 04 '16 at 12:19
I will try this... – Heril Muratovic Apr 04 '16 at 12:23
should $date1 and $date2 be strtotime values? – Heril Muratovic Apr 05 '16 at 12:22
Have you got some errors ? What is the output ? STRTOTIME: `The function expects to be given a string containing an English date format and will try to parse that format into a Unix timestamp (the number of seconds since January 1 1970 00:00:00 UTC), relative to the timestamp given in now, or the current time if now is not supplied.` – androidnation Apr 05 '16 at 14:51

score 3 · Accepted Answer · answered Aug 04 '16 at 19:16

In general, you can generate a series of N integers in MySQL using:

    select (@i:=@i+1)-1 as `myval` from someTable,(SELECT @i:=0) gen_sub limit N

Note that the table that you join on (someTable) must have at least N rows. The -1 above is to make it base-zero... remove it and you'll get 1,2,3 for N=3.

You can feed those integers into the DATE_ADD function to turn it into a series of dates. To make it easier to follow, let's use some user variables for the dates.

SET @date_min = '2016-03-04';
SET @date_max = '2016-03-10';

select DATE_ADD(@date_min, INTERVAL (@i:=@i+1)-1 DAY) as `date`
from information_schema.columns,(SELECT @i:=0) gen_sub
where DATE_ADD(@date_min,INTERVAL @i DAY) BETWEEN @date_min AND @date_max

That will return rows for those days and every day between them. Now it's just a matter of joining against your table... I haven't tried it since I don't have your db structure, but something like the following should work:

SET @date_min = '2016-03-04';
SET @date_max = '2016-03-10';

SELECT
   date_generator.date,
   ifnull(SUM(val1),0) as sum_val
from (
   select DATE_ADD(@date_min, INTERVAL (@i:=@i+1)-1 DAY) as `date`
   from information_schema.columns,(SELECT @i:=0) gen_sub 
   where DATE_ADD(@date_min,INTERVAL @i DAY) BETWEEN @date_min AND @date_max
) date_generator
left join table1 on table1.date = date_generator.date
GROUP BY date;

RJParikh · Answer 3 · 2016-04-04T12:42:45.700

0

Use below code its similar to your query and surely work and helful to you.

$query = "SELECT date, SUM(val1) as sum_val  
FROM table1  
WHERE date BETWEEN '2016-03-04' AND '2016-03-10'  
GROUP BY date";
$result = mysql_db_query($db,$query);
$dateArray = Array();
foreach($result as $row)
{
    $dateArray[] = strtotime($row['date']);
}


$from = "1-4-2016";
$to = "30-4-2016";
$datediff = strtotime($from) - strtotime($to);
$days = floor($datediff/(60*60*24));
for ($d1 = 1; $d1<$days; $d1++) {
    $cdate = strtotime("+".$d1." day", strtotime($from));
    if(!in_array($cdate, $dateArray))
    {
        // Insert record with zero
    }
}

edited Apr 04 '16 at 12:42

answered Apr 04 '16 at 12:26

RJParikh

4,096
1
19
36

but why for 30 days? – Heril Muratovic Apr 04 '16 at 12:27
its for one month and if you want to make it dynamic then we need to find total days between from and to dates and just pass it in $d1<$totalDays. @HerilMuratovic – RJParikh Apr 04 '16 at 12:40
yeah, I need it to be dynamic. – Heril Muratovic Apr 04 '16 at 12:43
Please check edited answer it works surely for you. @HerilMuratovic – RJParikh Apr 04 '16 at 12:43
Please provide your feedback. @HerilMuratovic – RJParikh Apr 04 '16 at 12:46
give me some time... I will check your answer if it works for me. – Heril Muratovic Apr 04 '16 at 12:54
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/108181/discussion-between-ruchish-parikh-and-heril-muratovic). – RJParikh Apr 04 '16 at 12:58

MySQL - fill missing dates

3 Answers3

Linked