Query to find the count based on 15 minutes on datetime column

mysql> show create table test3 \G
*************************** 1. row ***************************
       Table: test3
Create Table: CREATE TABLE `test3` (
  `eno` int(11) NOT NULL AUTO_INCREMENT,
  `created_date` datetime DEFAULT NULL,
  `errno` int(11) DEFAULT NULL,
  PRIMARY KEY (`eno`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


+-----+---------------------+-------+
| eno | created_date        | errno |
+-----+---------------------+-------+
|   1 | 2011-12-19 11:00:28 |   100 |
|   2 | 2011-12-19 11:14:31 |   101 |
|   3 | 2011-12-19 11:14:38 |   103 |
|   4 | 2011-12-19 11:00:28 |   100 |
|   5 | 2011-12-19 12:00:59 |   100 |
|   6 | 2011-12-19 11:15:06 |   101 |
|   7 | 2011-12-19 11:15:33 |   100 |
|   8 | 2011-12-19 11:15:34 |   100 |
|   9 | 2011-12-19 11:30:28 |   100 |
|  10 | 2011-12-19 11:30:28 |   100 |
+-----+---------------------+-------+
10 rows in set (0.00 sec)

output Method 1:


mysql> SELECT
    -> DATE(created_date) AS Date,
    -> CONCAT(
    -> HOUR(created_date), ':',
    -> CASE
    -> WHEN MINUTE(created_date) BETWEEN 0 AND 14 THEN '00'
    -> WHEN MINUTE(created_date) BETWEEN 15 AND 29 THEN '15'
    -> WHEN MINUTE(created_date) BETWEEN 30 AND 44 THEN '30'
    -> WHEN MINUTE(created_date) BETWEEN 45 AND 59 THEN '45'
    -> END
    -> )AS Per15min, COUNT(*) as 'Error Counter'
    -> FROM test3
    -> where errno=100
    -> GROUP BY Per15min;
+------------+----------+---------------+
| Date       | Per15min | Error Counter |
+------------+----------+---------------+
| 2011-12-19 | 11:00    |             2 |
| 2011-12-19 | 11:15    |             2 |
| 2011-12-19 | 11:30    |             2 |
| 2011-12-19 | 12:00    |             1 |
+------------+----------+---------------+
4 rows in set (0.00 sec)




method2:

mysql> select x.created_date,x.hdate,count(errno),x.minute1,if(x.minute1=1,
    -> concat(x.hdate, "-", x.hdate, ":15") ,
    -> if(x.minute1=2,concat(x.hdate,":15", "-" , x.hdate,":30"),
    -> if(x.minute1=3,concat(x.hdate,":30", "-", x.hdate,":45"),
    -> if(x.minute1=4,concat(x.hdate,":45", "-", x.hdate + 1),"xx")))) as xxx  from
    -> (
    ->  select date(created_date) as created_date ,hour(created_date) hdate ,errno ,case when minute(created_date) < 15 then 1
    ->  when minute(created_date) < 30 then 2 when minute(created_date) < 45 then 3 when minute(created_date) < 59 then 4 end as minute1
    ->  from test3   where errno =100
    ->  ) x
    ->  group by x.created_date,x.hdate,x.minute1;
+--------------+-------+--------------+---------+-------------+
| created_date | hdate | count(errno) | minute1 | xxx         |
+--------------+-------+--------------+---------+-------------+
| 2011-12-19   |    11 |            2 |       1 | 11-11:15    |
| 2011-12-19   |    11 |            2 |       2 | 11:15-11:30 |
| 2011-12-19   |    11 |            2 |       3 | 11:30-11:45 |
| 2011-12-19   |    12 |            1 |       1 | 12-12:15    |
+--------------+-------+--------------+---------+-------------+
4 rows in set (0.00 sec)

SELECT
DATE(created_date) AS Date,
CONCAT(
HOUR(created_date), ':',
CASE
WHEN MINUTE(created_date) BETWEEN 0 AND 14 THEN '00'
WHEN MINUTE(created_date) BETWEEN 15 AND 29 THEN '16'
WHEN MINUTE(created_date) BETWEEN 30 AND 44 THEN '31'
WHEN MINUTE(created_date) BETWEEN 45 AND 59 THEN '46'
END,
' TO ',
HOUR(created_date), ':',
CASE
WHEN MINUTE(created_date) BETWEEN 0 AND 14 THEN '15'
WHEN MINUTE(created_date) BETWEEN 15 AND 29 THEN '30'
WHEN MINUTE(created_date) BETWEEN 30 AND 44 THEN '45'
WHEN MINUTE(created_date) BETWEEN 45 AND 59 THEN '59'
END
) AS Per15min, COUNT(*) as 'Error Counter'
FROM test3
GROUP BY Per15min;
--- OUTPUT ---
+------------+----------------+---------------+
| Date | Per15min | Error Counter |
+------------+----------------+---------------+
| 2011-12-19 | 11:16 TO 11:30 | 1 |
| 2011-12-19 | 11:31 TO 11:45 | 1 |
| 2011-12-19 | 11:46 TO 11:59 | 3 |
| 2011-12-19 | 12:00 TO 12:15 | 1 |
| 2011-12-19 | 12:16 TO 12:30 | 2 |
| 2011-12-19 | 12:31 TO 12:45 | 1 |
| 2011-12-19 | 12:46 TO 12:59 | 2 |
| 2011-12-19 | 13:00 TO 13:15 | 1 |
+------------+----------------+---------------+
The easies way of writing the query is as follows:

#####15 minutes query #######
SELECT date(creation_date) as day1,HOUR(creation_date) AS hour,
CONCAT(LPAD(FLOOR(MINUTE(creation_date)/15)*15, 2, '0'), '-',LPAD((FLOOR(MINUTE(creation_date)/15)+1)*15,2,'0')) AS range1,
COUNT(*) AS 'ErrorCounter'
FROM   transaction
WHERE DATE(creation_date) = '2011-06-26' 
GROUP BY day1, hour,range1
ORDER BY day1, hour,range1 ;

### 5 minutes query ####

SELECT date(creation_date) as day1,HOUR(creation_date) AS hour,
CONCAT(LPAD(FLOOR(MINUTE(creation_date)/5)*5, 2, '0'), '-',LPAD((FLOOR(MINUTE(creation_date)/5)+1)*5,2,'0')) AS range1,
COUNT(*) AS 'ErrorCounter'
FROM   transaction
WHERE DATE(creation_date) = '2011-06-26'
GROUP BY day1, hour,range1
ORDER BY day1, hour,range1 ;