CODEWITHSUNDEEP
mysqlsqldatetime
Sam Stoelinga
Sam Stoelinga

Reputation: 5021

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 this 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.

Upvotes: 1

Views: 929

Answers (2)

Devart
Devart

Reputation: 121932

To group by minute you could use this clause -

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

Upvotes: 1

ljkyser
ljkyser

Reputation: 1019

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.

Upvotes: 2

Related Questions