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 ;

steps for configurations SSL in mysql

1.) Step one SSL should be enabled if it is not enabled then we need to enable "have_openssl" and "have_ssl"
mysql> show variables like '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_key       |          |
+---------------+----------+
7 rows in set (0.00 sec)

2.) in my.cnf add ssl below [mysqld] as follow and restart the server.
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
#### ssl configurations
ssl

3.) after restart the server check the ssl is enabled or not
mysql> SHOW variables LIKE '%ssl%';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| have_openssl  | YES                       |
| have_ssl      | YES                       |
| ssl_ca        |
| ssl_capath    |                           |
| ssl_cert      |                           |
| ssl_cipher    |
| ssl_key       |                           |
+---------------+---------------------------+
7 rows in set (0.00 sec)

4.) creating the certificates.
 for creating the certificates we first need to install openssl
 check wheather openssl is installed or not
 openssl
OpenSSL>
5. creating the certficates.
shell> mkdir -p /etc/mysql/cert 
shell> cd /etc/mysql/cert
Create CA certificate:
shell> openssl genrsa 2048 > ca-key.pem
shell> openssl req -new -x509 -nodes -days 9999 -key ca-key.pem > ca-cert.pem
Create server certificate:
shell> openssl req -newkey rsa:2048 -days 9999 -nodes -keyout server-key.pem > server-req.pem
shell> openssl x509 -req -in server-req.pem -days 9999 -CA ca-cert.pem -CAkey ca-key.pem –set_serial 01 > server-cert.pem
Create client certificate:
shell> openssl req -newkey rsa:2048 -days 9999 -nodes -keyout client-key.pem > client-req.pem
shell> openssl x509 -req -in client-req.pem -days 9999 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
verify the certificates
openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
Now we have following  certificates/keys got created.
ca-cert.pem
ca-key.pem
client-cert.pem
client-key.pem
client-req.pem
server-cert.pem
server-key.pem
server-req.pem

6.) now we need to add certificates in my.cnf as follows:
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
#### ssl configurations
ssl
ssl-ca = /etc/cert/ca-cert.pem
ssl_capath = /etc/cert
ssl-cert = /etc/cert/server-cert.pem
ssl-key = /etc/cert/server-key.pem
[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
ssl-ca    =   /etc/cert/ca-cert.pem
ssl-cert  =  /etc/cert/client-cert.pem
ssl-key   = /etc/cert/client-key.pem
7.) restart the mysql
shell> /etc/init/mysql restart
 run the command as follows:
mysql> SHOW variables LIKE '%ssl%';
+---------------+---------------------------+
| Variable_name | Value                     |
+---------------+---------------------------+
| have_openssl  | YES                       |
| have_ssl      | YES                       |
| ssl_ca        | /etc/cert/ca-cert.pem     |
| ssl_capath    | /etc/cert                 |
| ssl_cert      | /etc/cert/server-cert.pem |
| ssl_cipher    |                           |
| ssl_key       | /etc/cert/server-key.pem  |
+---------------+---------------------------+
7 rows in set (0.00 sec)

9.) given the grant privileges uing ssl.
GRANT ALL ON *.* TO 'test123'@'localhost' IDENTIFIED BY 'test123' REQUIRE SSL;

10.) suppose we want to connect to another server (from system2).
copy the ca-cert.pem,client-cert.pem,client-key.pem to sytem2
11.) go  to system2 type the following command
mysql -u test123 -h system1 --ssl-ca=/etc/cert1/ca-cert.pem --ssl-cert=/etc/cert1/client-cert.pem --ssl-key=/etc//cert1/client-key.pem -p
mysql>
In this way the ssl configuration is established.
for vericication the command is as follows:

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.5.13, for Linux (x86_64) using readline 5.1
Connection id:          25
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.13 MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /var/lib/mysql/mysql.sock
Uptime:                 23 hours 50 min 36 sec
Threads: 2  Questions: 10559  Slow queries: 0  Opens: 1261  Flush tables: 1  Open tables: 42  Queries per second avg: 0.123
If we want to set up replication using ssl keys.
The commands are as follows:

mysql> GRANT REPLICATION SLAVE ON *.* TO "repl"@"10.%" IDENTIFIED BY 'XXXXXX'  REQUIRE SSL;
mysql> FLUSH PRIVILEGES;
the replication command are as follows:

CHANGE MASTER TO
MASTER_HOST=,
MASTER_USER='repl',
MASTER_PASSWORD='',
MASTER_LOG_FILE='',
MASTER_LOG_POS= ,
MASTER_SSL=1,
MASTER_SSL_CA = '/etc/mysql/cert/ca-cert.pem',
MASTER_SSL_CERT = '/etc/mysql/cert/client-cert.pem',
MASTER_SSL_KEY = '/etc/mysql/cert/client-key.pem';

For more details the url are as follows:
http://dev.mysql.com/doc/refman/5.5/en/secure-using-ssl.html