April 27, 2009

Group results by second/minute/hour in MySQL

If you need the results from you query to be grouped by date/hour/minute, you may need a bit of sql trick.

This is a simple MySQL tip, but it helped me a lot.

We have a traffic table which monitors every single coming traffic to our application. I wanted to get the real values for how many requests I we have per Second / Minute / Hour and also what times of the the day are high traffic and what times are low traffic, to do some load-balancing on the application and server.

The query is very simple, just COUNT the number and do a GROUP BY and extract the minute/hour/…

Like :

SELECT tr_date_time,count(*) AS NUM FROM `traffic`
    WHERE DATE(`tr_date_time`) = '2009-04-25'
    GROUP BY EXTRACT(HOUR_MINUTE FROM tr_date_time);

The script above extracts the number of hits per minute during the day. Don’t forget that if you want to have the result by minutes, you have to add the hour as well to make it unique.

p.s: I hope your Date/Time/DateTime field is human friendly not Unix_timestamp, which will add some more extra calculations on the result set.

comments powered by Disqus
comments powered by Disqus