Filling gaps in dates

[_what]

So i'm doing a query for sales data and my return array looks like this

 

('date' => '2008-11-01 00:00:00', 'total' => 1),

('date' => '2008-11-03 00:00:00', 'total' => 3),

('date' => '2008-11-04 00:00:00', 'total' => 3),

('date' => '2008-11-07 00:00:00', 'total' => 5),

('date' => '2008-11-12 00:00:00', 'total' => 1)

 

There are obviously gaps in my dates.  What i'm trying to figure out is the best approach to filling those empty dates with a total of 0 so my graphs don't look all jacked up because i'm not providing all the data.  Does anyone have an idea how to approach this problem to fill in the missing dates?  I'd like the array to look like the following:

 

 

('date' => '2008-11-01 00:00:00', 'total' => 1),

('date' => '2008-11-02 00:00:00', 'total' => 0),

('date' => '2008-11-03 00:00:00', 'total' => 3),

('date' => '2008-11-04 00:00:00', 'total' => 3),

('date' => '2008-11-05 00:00:00', 'total' => 0),

('date' => '2008-11-06 00:00:00', 'total' => 0),

('date' => '2008-11-07 00:00:00', 'total' => 5),

('date' => '2008-11-08 00:00:00', 'total' => 0),

('date' => '2008-11-09 00:00:00', 'total' => 0),

('date' => '2008-11-10 00:00:00', 'total' => 0),

('date' => '2008-11-11 00:00:00', 'total' => 0),

('date' => '2008-11-12 00:00:00', 'total' => 1)

 

[PFMaBiSmAd]

From the standpoint of doing this all through a database, having a table (either a permanent one or a temporary one) that holds a contiguous range of dates that spans the date range of your report that you can join your data with, is how this is usually done.

 

Assuming you have a start and end date already that you are using to get the data, take a look at this link - http://www.richnetapps.com/using-mysql-generate-daily-sales-reports-filled-gaps/

 

I would personally use a TEMPORARY table in the fill_calendar() procedure so that you don't need to worry about making sure that it contains a correct range of dates. Just call fill_calendar() with the same start and end date that you are using in your query to fetch the data.

[_what]

Thanks for the response.  It sounded very promising.  I created a calendar table and filled it with about 5 years of dates to cover my range.  The problem is everytime i try to right join the calendar table to my orders table it locks up mysql.  I have about 50,000 rows in my orders table. 

 

Here's my sample test query:

 

SELECT calendar.datefield AS date

FROM orders

RIGHT JOIN calendar ON ( DATE( orders.date_purchased ) = calendar.datefield )

WHERE calendar.datefield > '2009-11-30'

AND calendar.datefield < '2009-12-5'

GROUP BY date

 

That small query alone takes almost a half second.

 

here's my explain:

 

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE calendar range PRIMARY PRIMARY 3 NULL 6 Using where; Using index; Using temporary; Using f...

1 SIMPLE orders index NULL date_purchased 9 NULL 48041 Using index

 

[_what]

Anyone?

[_what]

Ok i figured out a solution to it.  Does not involve any database stuff but this should work well for my purposes:

 

$day = 86400;
$now = time();
$days = 90;

$date_array = array();

for($i = 0; $i < $days; $i++)
{
$date_array[$i]['date'] = date('Y-m-d', ($now + ($i * $day)));
$date_array[$i]['qty'] = 0;
}

 

I then take that date array and merge it with my sales data.