How to group by minute within a timerange and display 0 values in MySQL?

I want to group by every minute between date1 and date2 in the following query. Also the 0 values should be displayed. So every minute between the dates should be displayed. The problem is that those dates doesn't exist in the table.

SELECT 
click.date as 'date',
COUNT(*)
FROM click 
INNER JOIN short_url surl ON click.short_url_id = surl.id
INNER JOIN article_surl ON article_surl.article_id = 3
WHERE click.date BETWEEN (?Date1:'2011-04-24 13:33:00') AND (?Date2:'2011-04-24 15:33:00')
GROUP BY DATE_FORMAT(click.date, '%Y-%m-%d %H:%i:00');

I think the only way to t开发者_JAVA百科his is to make a temporary table? What are the performance hits of this? A join on date also isn't so good, because the click.date may also have milliseconds in the datetime. So a DateFormat should be used? Maybe it is better to this in my application logic, read the array and fill in the missing dates with 0 values.

I think your suggestion of filling in the missing values in your application logic is the best bet to avoid creating a temp table, especially if you will be processing the array anyways. We do the same thing in some of our services we've written.

To group by minute you could use this clause -

...
GROUP BY DAYOFYEAR(click.date), (60 * HOUR(click.date) + MINUTE(click.date))