group by timeperiod only shows non zero totals

I am doing a mysql query to count the number of entries each minute and graphing the result. The result out开发者_Go百科put only shows the timestamps where the amount isn't zero. This is causing my graph to provide incorrect results as gaps in times it draws a line between the two points. I would like to return each minute and the number of values.

Is there a way to build this query without having to loop and do multiple queries? Or if I need a loop is there an easy way to loop through each minute between two timestamps?

This is my query:

SELECT UNIX_TIMESTAMP(timestamp) AS timestamp, count(*) AS total  
FROM table  
WHERE timestamp <= '2011-04-22 11:00' AND timestamp >= '2011-04-20 10:00'  
GROUP BY MINUTE(timestamp)

What you want to do is use a "tally" or "numbers" table. Basically, you create a table that has one integer column with numbers incrementing from 1 to a very high number. So, if the tally table has 1000 records, the numbers from 1 to 1000 are in the table. You'll want to put more numbers in it than 1000 but hopefully you get the idea.

Once you have your tally table (click here for a script), you would select it into your query where the numtablevalue is between 1 and enddate - startdate (in minutes). And then group by startdate + numtablevalue.

So something like this (pseudo SQL) should work:

SELECT 'startdate' + INTERVAL tt.id MINUTE, COUNT(t1.timestampfield)
FROM tallytable AS tt
INNER JOIN
table AS t1
ON 'startdate' + INTERVAL tt.id MINUTE = t1.timestampfield
WHERE
tt.id BETWEEN 1 AND GETMINUTES('enddate' - 'begindate')
GROUP BY 'startdate' + INTERVAL tt.id MINUTE

Basically we're selecting the numbers from 1 to the number of seconds between the two dates. Then we use the startdate + tt.id to get each second between the dates. Then we join and group on that to find the number of records from t1 that fall into each minute as selected using the number table. I hope this makes sense.

There are a few things you may want to note:

• This will get times > startdate (as opposed to >= startdate)
• Be careful if your timestamps have seconds associated with them since 5:12 PM may not be equal to 5:12:30 PM

select i.`minute`,count(minute(timestamp)) as total from (
select tmp.digit + tmp2.digit * 10 as `minute`
from (
select 0 as digit 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 tmp
cross join (
select 0 as digit union all 
select 1 union all 
select 2 union all 
select 3 union all 
select 4 union all 
select 5
) as tmp2 ) as i
left join table
on minute(tb.timestamp) = i.`minute`
and timestamp between '2011-04-20 10:00' and '2011-04-22 11:00' 
group by i.`minute`

This query will give you 60 records (minutes from 0 to 59) with total number for each minute without creating another table to store integer values.