Group MySQL Data into Arbitrarily Sized Time Buckets

Question

How do I count the number of records in a MySQL table based on a timestamp column per unit of time where the unit of time is arbitrary?

Specifically, I want to count how many record's timestamps fell into 15 minute buckets during a given interval. I understand how to do this in buckets of 1 second, 1 minute, 1 hour, 1 day etc. using MySQL date functions, e.g.

SELECT YEAR(datefield) Y, MONTH(datefield) M, DAY(datefield) D, COUNT(*) Cnt FROM mytable GROUP BY YEAR(datefield), MONTH(datefield), DAY(datefield)

but how can I group by 15 minute buckets?

Answer 1

How about getting the INTEGER of ( HOUR( DateField ) * 60 + MINUTES( DateField )) / YourInterval.

This way, no matter what your interval, by including the hour * 60 minutes will keep it sequentially during a given day and you won't be dealing with just 15 minutes grouped per hour, but each 15 minutes of its OWN hour... Add that column to your group by clause which you can do by its ordinal position.

Answer 2

GROUP BY
    YEAR(datefield),
    MONTH(datefield),
    DAY(datefield),
    HOUR(datefield),
    FLOOR(MINUTE(datefield)/15)

You could alternatively say just:

SELECT FLOOR(UNIX_TIMESTAMP(datefield)/900) AS t, COUNT(*) AS cnt
FROM mytable
GROUP BY t

with the application responsible for formatting the timestamp for each 15-minute period back out to readable y/m/d/h/m. (If you needed to have local time in a crazy non-quarter-hour-aligned timezone, you'd need a hack offset though.)

Answer 3

Just to get you thinking, here's an alternative to the FLOOR(MINUTE(datefield)/15) solution:

GROUP BY CASE
WHEN MINUTES(datetime) BETWEEN 0 AND 14 THEN 0
WHEN MINUTES(datetime) BETWEEN 15 AND 29 THEN 1
WHEN MINUTES(datetime) BETWEEN 30 AND 44 THEN 2
WHEN MINUTES(datetime) BETWEEN 45 AND 59 THEN 3
END