MySQL – group results by second/minute/hour

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 applciation. 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 calcuations on the result set.

Advertisements

2 thoughts on “MySQL – group results by second/minute/hour

  1. @Red
    MINUTE(tr_date_time) will return only return the minute part. so you will have 00,01,02,03…..58,59,00,01,02
    It will loop only between 00 and 59, so you can not clearly distinguish between the records.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s