Group mysql query by 15 min intervals
Solution 1
SELECT FLOOR(UNIX_TIMESTAMP(timestamp)/(15 * 60)) AS timekey
FROM table
GROUP BY timekey;
Solution 2
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
Solution 3
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:
1)
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.
Solution 4
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.
Solution 5
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
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:15concat( 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.
gsiener
Updated on July 09, 2022Comments
-
gsiener almost 2 years
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?