Group Datetime into 5,15,30 and 60 Minute Intervals

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;

Grouping timestamp every 30 minutes

Here's the basic query to group by 30 minutes interval.

SELECT 
FROM_UNIXTIME(ROUND(UNIX_TIMESTAMP(timestamp)/(30* 60)) * (30*60)) thirtyHourInterval
FROM tablename
GROUP BY ROUND(UNIX_TIMESTAMP(timestamp)/(30* 60));

Note: ROUND() may lead you towards wrong output. Use the following query instead. Look at the following example:

SELECT ROUND(3.7), ROUND(4.2);

Result: 4 4.

Both lies in the same segment. The same holds for the above query while rounding the timestamp of different segments might fall in the same segment thus leading towards wrong output

[The following query is Recommended]

SELECT 
FROM_UNIXTIME((UNIX_TIMESTAMP(`timestamp`) DIV (30* 60) ) * (30*60)) thirtyHourInterval
FROM tablename
GROUP BY UNIX_TIMESTAMP(`timestamp`) DIV (30* 60)

SQL FIDDLE DEMO

---

Alternatively you can adopt the following query.

SELECT 
FROM_UNIXTIME(ROUND(UNIX_TIMESTAMP(timestamp)/(30* 60)) * (30*60)) thirtyHourInterval
FROM tablename
GROUP BY ( 4 * HOUR( `timestamp` ) + FLOOR( MINUTE( `timestamp` ) / 30 ));

Relatedpost

How can I group time by hour or by 10 minutes?

finally done with

GROUP BY
DATEPART(YEAR, DT.[Date]),
DATEPART(MONTH, DT.[Date]),
DATEPART(DAY, DT.[Date]),
DATEPART(HOUR, DT.[Date]),
(DATEPART(MINUTE, DT.[Date]) / 10)

Group mysql query by 15 min intervals

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

SQL SERVER - Group records by n minutes interval

GROUP BY 
...
(DATEDIFF(MINUTE, 0, SDate) / @n)

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;

How do i get the full hour instead of the minutes?

Use date maths and a "magic" date:

DATEADD(HOUR,DATEDIFF(HOUR,0,YourColumn),0);

This gets the number of hours between the "date" 0 (1900-01-01) and your date value, and then adds that many hours to the "date" 0.

On SQL Server 2022 (currently in preview), however, you have access to DATETRUNC and DATE_BUCKET that make this much easier:

DATETRUNC(HOUR,YourColumn),
DATE_BUCKET(HOUR,0,YourColumn)

---

Related Topics