group data by time interval

SQL Apprentice Question
What SQL will group timed events, so I can see how many records were recorded
in each 10 minute interval. There may be no data for some 10 minute
intervals, in which case I must report zero.

for example...with raw data in a table as
2006/06/19 12:00:00 record 1
2006/06/19 12:05:00 record 2
2006/06/19 12:21:00 record 3
2006/06/19 12:22:00 record 4
2006/06/19 12:23:00 record 5
2006/06/19 12:24:00 record 6


result should be
2 records for 12:00-12:10
0 records for 12:10-12:20
4 records for 12:20-12:30

Celko Answers
>> What SQL will group timed events, so I can see how many records [sic] were recorded in each 10 minute interval. <<


Populate a table with the desired time slots, thus:

CREATE TABLE ReportTimeslots
(range_name CHAR(18) NOT NULL PRIMARY KEY,
start_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
CHECK (start_time < end_time));


Then do your query.


SELECT R.range_name, COUNT(*) AS event_cnt
FROM ReportTimeslots AS R
LEFT OUTER JOIN
Events AS E
ON E.event_time BETWEEN R.start_time AND R.end_time
GROUP BY R.range_name;


Another trick is a VIEW that sets itself each day by using the
CURRENT_TIMESTAMP for the range_name and does a little temporal math to
get the proper start and end times.