GROUP BY a SQL query by N minutes

2 Feb

Just a quick post about a tip to group in SQL [tested on MySQL] a date-time field by N number of minutes using FLOOR().

Functions such as DAY(), HOUR(), MINUTE() are very useful to group by dates, but what about 30, 15, or 10 minutes?

Here is what I use almost daily:

((60/N) * HOUR(date_time_field ) + FLOOR( MINUTE(date_time_field) / N ))

Where N is the number of minutes you want to group by, i.e. 10
And date_time_field is you date-time field in the table.

I use it regularly for complex queries, it seems to be fairly efficient. It was originally taken from this great query list:

http://www.artfulsoftware.com/infotree/queries.php?&bw=1072

There are probably many ways to doing it, anyone another tip about it?