Grouping into Interval of 5 Minutes Within a Time Range

Grouping in 5 minute intervals within a time range

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`

How to group time column into 5 minute intervals and max/min value respectively SQL?

Sorry if I'm repeating another answer. I'll delete if I am..

SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(datetime)/300)*300) x
     , MIN(value) min_value
     , MAX(value) max_value 
  FROM my_table 
 GROUP 
    BY x;

Explain MySQL Query for How to group time column into 5 minute intervals

The answer is in the token DIV.

DIV is not only dividing (/), but also casting to integer implicitly.

You do this to get an integer number without remainder. Basically, this is the same as using an explicit cast after dividing, or using ROUND() or FLOOR() after dividing.

Ref:

https://dev.mysql.com/doc/refman/8.0/en/arithmetic-functions.html

Integer division. Discards from the division result any fractional
part to the right of the decimal point.

select to group date and time in 5-minute intervals

Thank you all. As to why there is a separate VARCHAR2 column for the time component, the tables were originally migrated from some legacy database that had a date type but without a time component the latter which was stored as a string. Here is my own idea which gives me exactly what I want:

select tDATE,substr(tTIME,1,3)||
         case 
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 0 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 5 then '00' 
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 5 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 10 then '05'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 10 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 15 then '10'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 15 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 20 then '15'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 20 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 25 then '20'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 25 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 30 then '25'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 30 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 35 then '30'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 35 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 40 then '35'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 40 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 45 then '40'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 45 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 50 then '45'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 50 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 55 then '50'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 55 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 60 then '55'
           else '00'
         end as tTIME
,count(*)
from TAB1
group by tDATE,substr(tTIME,1,3)||
         case 
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 0 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 5 then '00' 
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 5 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 10 then '05'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 10 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 15 then '10'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 15 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 20 then '15'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 20 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 25 then '20'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 25 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 30 then '25'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 30 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 35 then '30'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 35 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 40 then '35'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 40 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 45 then '40'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 45 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 50 then '45'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 50 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 55 then '50'
           when to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) >= 55 and to_number(regexp_substr(substr(tTIME,4,2),'^[-]?[[:digit:]]*\.?[[:digit:]]*$')) < 60 then '55'
           else '00'
         end
order by 3 desc;

Group mysql query by 15 min intervals

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

MySQL - Grouping Time Ranges by Fifteen Minute Intervals

Alright. It took some doing, but I figured it out with RToyo's help:

First: create a list of fifteen minute intervals:

        select 
        addtime(time('00:00:00'),sec_to_time(900 * (a.a + b.a * 10))) as 'IntervalStart',
        addtime(time('00:14:59'),sec_to_time(900 * (a.a + b.a * 10))) as 'IntervalEnd'
    from (
        select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    where
        addtime(time('00:00:00'),sec_to_time(900 * (a.a + b.a * 10))) between '06:00:00' and '18:45:00'

Next: join the Timekeeping table by testing the overlap between the StartTime and Endtime fields:

left join (     select 
        addtime(time('00:00:00'),sec_to_time(900 * (a.a + b.a * 10))) as 'Interval Start',
        addtime(time('00:14:59'),sec_to_time(900 * (a.a + b.a * 10))) as 'Interval End'
    from (
        select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
        cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
    where
        addtime(time('00:00:00'),sec_to_time(900 * (a.a + b.a * 10))) between '06:00:00' and '18:45:00') as Intervals
on timekeepingtable.StartTime <= Intervals.IntervalStart
and timekeepingtable.EndTime >= Intervals.IntervalEnd

Then just modify the StartTime and EndTime fields to be bounded by the intervals:

        if(time(pgrp.StartTime) < Intervals.IntervalStart,Intervals.IntervalStart,time(pgrp.StartTime)) as 'Start Time',
    if(time(pgrp.EndTime) > Intervals.IntervalEnd, Intervals.IntervalEnd,time(pgrp.EndTime)) as 'End Time',