Yes, you can do that using SQL only. A solution would be to use a Stored Routine. The bellow Stored Procedure produces following output:
start cnt
00:05:00 1
00:10:00 0
00:15:00 1
00:20:00 0
00:25:00 1
00:30:00 0
00:35:00 1
00:40:00 0
00:45:00 0
00:50:00 0
00:55:00 2
The table I used:
CREATE TABLE `timedata` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` datetime DEFAULT NULL,
`c2` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
)
Here the Stored Procedure (adjust for your environment):
DROP PROCEDURE IF EXISTS per5min;
DELIMITER //
CREATE PROCEDURE per5min ()
BEGIN
DECLARE dtMin DATETIME;
DECLARE dtMax DATETIME;
DECLARE dtStart DATETIME;
DECLARE dtStop DATETIME;
DECLARE tmDiff TIME;
DECLARE result INT UNSIGNED;
SET @offset = 5 * 60;
SELECT MIN(c1) into dtMin FROM timedata;
SELECT MAX(c1) into dtMax FROM timedata;
CREATE TEMPORARY TABLE tmp_per5min (
start TIME,
cnt INT UNSIGNED
);
SET dtStart = dtMin;
REPEAT
SELECT dtStart + INTERVAL @offset SECOND into dtStop;
SELECT count(c2) into result FROM timedata WHERE c1 BETWEEN dtStart and dtStop;
SELECT TIME(SUBTIME(dtStop,TIME(dtMin))) into tmDiff;
INSERT INTO tmp_per5min (start,cnt) VALUES (tmDiff,result);
SET dtStart = dtStop;
UNTIL dtStop >= dtMax END REPEAT;
SELECT * FROM tmp_per5min;
DROP TABLE tmp_per5min;
END;
//
DELIMITER ;
CALL per5min();
If you save the above into a file called 'per5minproc.sql', you can load it like this:
shell> mysql -uroot test < per5minproc.sql
In Python using MySQLdb (I didn't get this working in MySQL Connector/Python, me ashamed!):
import MySQLdb as m
if __name__ == '__main__':
db = m.connect(user='root',db='test')
c = db.cursor()
c.callproc("per5min")
print(c.fetchall())
c.close()
db.close()
The solution above works, but probably will need some tweaking, e.g. dtStart can be an argument to the SP.
And, it's indeed all SQL!