Grouping in 5 minute intervals within a time range

Question

I have some difficulties with the MySQL commands that I want to do.

I need to generate some graphs obtaining a query in MySQL in intervals, for example, of 5 minutes generating an average of a data with the AVG method.

These are my data.

How do I group them into results with 5 minute intervals?

I already generated a query:

SELECT FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP(created_at)) DIV 300)*300) AS t, site_id, AVG(response_time) as c 
FROM sites_health_metrics 
WHERE site_id = 1 
AND created_at BETWEEN '2021-08-23 20:40:00' AND '2021-08-23 21:40:00' 
GROUP BY t ORDER BY t

where I get the following:

I want my output to be like:

Check https://stackoverflow.com/questions/701444/how-do-i-make-a-row-generator-in-mysql and https://stackoverflow.com/questions/6870499/generate-series-equivalent-in-mysql — NeeL, Aug 24 '21 at 22:05

score 2 · Accepted Answer · answered Aug 25 '21 at 18:29

This is work


SELECT series.`range` as `range`, COALESCE(dt.average, 0) as average FROM 
(
    SELECT FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP(created_at)) DIV 300)*300) AS fecha, site_id, AVG(response_time) as average
    FROM sites_health_metrics WHERE site_id = 1 
    AND created_at BETWEEN '2021-08-23 20:40:00' AND '2021-08-23 21:40:00'
    GROUP BY fecha
) dt
    RIGHT JOIN 
(
    SELECT 
    date_format(date_add('2021-08-23 20:40:00', INTERVAL @num:=@num+300 SECOND), '%Y-%m-%d %H:%i:%s') `range`
    FROM sites_health_metrics , (select @num:=-300) num LIMIT 
) series
ON series.`range` = dt.fecha
GROUP BY `range`

Matt · Answer 2 · 2021-08-24T19:10:32.997

0

SELECT UNIX_TIMESTAMP(created_at) DIV 300 t, site_id, AVG(response_time) c
FROM sites_health_metrics a
WHERE site_id = 1 
AND created_at BETWEEN '2021-08-23 20:40:00' AND '2021-08-23 21:40:00'
GROUP BY UNIX_TIMESTAMP(created_at) DIV 300, site_id

edited Aug 24 '21 at 19:10

answered Aug 24 '21 at 19:02

Matt

14,906
27
99
149

what's the `, id`? it doesn't look to me like you changed anything significant other than that – ysth Aug 24 '21 at 19:08
I need the 5 minute intervals that have no records to appear as well, to appear at 0 – Abner Gorostieta Aug 24 '21 at 19:42

Grouping in 5 minute intervals within a time range

2 Answers2