Group mysql query by 15 min intervals

I've got a monitoring system that is collecting data every n seconds (n is approximately 10 but varies). I'd like to aggregate the collected data by 15 minute intervals. Is there a way to consolidate the timestamp values into 15 minute chunks to allow for grouping to work?

SELECT   FLOOR(UNIX_TIMESTAMP(timestamp)/(15 * 60)) AS timekey
FROM     table
GROUP BY timekey;

Try this , grouping of records of 15 minutes interval, you can change 15*60 to the interval in seconds you need

SELECT sec_to_time(time_to_sec(datefield)- time_to_sec(datefield)%(15*60)) as intervals from tablename
group by intervals

Adaptation of approach 1) below:

select Round(date_format(date, "%i") / (15*60)) AS interval  
from table
group by interval 

Adaptation of approach 3) below:

SELECT Round(Convert(substring(date_column, 14, 2), UNSIGNED) / (15*60)) AS interval /* e.g. 2009-01-04 12:20:00 */
FROM table 
GROUP BY interval;

A few approaches I've found here:

select date_format(date, "%W") AS `Day of the week`, sum(cost)

from daily_cost
group by `Day of the week` 
order by date_format(date, "%w")

2)

select count(*) as 'count', 
  date_format(min(added_on), '%Y-%M-%d') as 'week commencing',
  date_format(added_on, '%Y%u') as 'week' 
from system 
where added_on >= '2007-05-16' 
group by week 
order by 3 desc;

3)

SELECT substring(postdate, 1,10) AS dd, COUNT(id) FROM MyTable GROUP BY dd;

(Also here: http://www.bradino.com/mysql/dayparting-on-datetime-field-using-substring/)

EDIT: All the solutions will perform badly on a table with a large number of records.

I started with the answer given above by unutbu but didn't get what I needed and had to add a bit to it.

Select Created, from_unixtime(FLOOR(UNIX_TIMESTAMP(Created)/(15*60))*(15*60)) GroupTime, COUNT(*) as Cnt FROM issue i GROUP BY GroupTime

This code divides by the 900 seconds in a 15 minute span then floors the value and multiplies it back up by 900, essentially rounding down to the nearest 15 minute increment.

Following query groups rows and creates timestamps at 15 min intervals.

Select concat( date(created_dt) , ' ', sec_to_time(time_to_sec(created_dt)- time_to_sec(created_dt)%(15*60) + (15*60)))as created_dt_new from table_name group by created_dt_new 

E.g Timestamps
2016-11-09 13:16:29
2016-11-09 13:16:49
2016-11-09 13:17:06
2016-11-09 13:17:26
2016-11-09 13:18:24
2016-11-09 13:19:59
2016-11-09 13:21:17

Are grouped into 2016-11-09 13:30:00

1. sec_to_time(time_to_sec(created_dt)- time_to_sec(created_dt)%(15*60) + (15*60)))
   Upper bounds time to nearest 15 min interval. e.g 12:10 -> 12:15

2. concat( date(created_dt) , ' ', sec_to_time(time_to_sec(created_dt)- time_to_sec(created_dt)%(15*60) + (15*60)))
   Generates a timestamp taking the date from the timestamp field.

Unix timestamps: floor them to nearest 15 minute using one of the following:

timestamp div (15 * 60) * (15 * 60) -- div is integer division operator
timestamp - timestamp % (15 * 60)

Date time: assuming the datatype does not have fractional seconds, floor them to nearest 15 minute using:

date - INTERVAL EXTRACT(SECOND FROM date) SECOND - INTERVAL EXTRACT(MINUTE FROM date) % 15 MINUTE

DBFiddle

This worked for me

mysql> **SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(NOW())- UNIX_TIMESTAMP(NOW())%(15*60));**
+---------------------------------------------------------------------+
| FROM_UNIXTIME(UNIX_TIMESTAMP(NOW())- UNIX_TIMESTAMP(NOW())%(15*60)) |
+---------------------------------------------------------------------+
| 2012-02-09 11:15:00                                                 |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)

THis Work for me

SELECT CONCAT (
		YEAR(transactionDate)
		,'-'
		,MONTH(transactionDate)
		,'-'
		,DAYOFMONTH(transactionDate)
		,' '
		,HOUR(transactionDate)
		,':'
		,((floor((MINUTE(transactionDate) / 15)) + 1) * 15) - 1
		,':59'
		) AS tmp1
	,count(*)
FROM tablename
GROUP BY tmp1 limit 20;

Change "15" to whatever interval you want.

select count(*),
CONCAT(HOUR(col_date),":",(MINUTE(create_date) div 15)*15) as date
from tablename
GROUP BY date
ORDER BY col_date ASC;

I was not satisfied by GROUP BY.

SELECT   datetime
FROM     table
WHERE MOD(MINUTE(TIME(datetime)),15) = 0 AND SECOND(TIME(datetime)) = 0;