Group DateTime into 5,15,30 and 60 minute intervals

Question

I am trying to group some records into 5-, 15-, 30- and 60-minute intervals:

SELECT AVG(value) as "AvgValue",
sample_date/(5*60) as "TimeFive"
FROM DATA
WHERE id = 123 AND sample_date >= 3/21/2012

i want to run several queries, each would group my average values into the desired time increments. So the 5-min query would return results like this:

AvgValue  TimeFive
6.90      1995-01-01 00:05:00
7.15      1995-01-01 00:10:00
8.25      1995-01-01 00:15:00

The 30-min query would result in this:

AvgValue  TimeThirty 
6.95      1995-01-01 00:30:00
7.40      1995-01-01 01:00:00

The datetime column is in yyyy-mm-dd hh:mm:ss format

I am getting implicit conversion errors of my datetime column. Any help is much appreciated!

Answer 1

Using

datediff(minute, '1990-01-01T00:00:00', yourDatetime)

will give you the number of minutes since 1990-1-1 (you can use the desired base date).

Then you can divide by 5, 15, 30 or 60, and group by the result of this division. I've cheked it will be evaluated as an integer division, so you'll get an integer number you can use to group by.

i.e.

group by datediff(minute, '1990-01-01T00:00:00', yourDatetime) /5

UPDATE As the original question was edited to require the data to be shown in date-time format after the grouping, I've added this simple query that will do what the OP wants:

-- This convert the period to date-time format
SELECT 
    -- note the 5, the "minute", and the starting point to convert the 
    -- period back to original time
    DATEADD(minute, AP.FiveMinutesPeriod * 5, '2010-01-01T00:00:00') AS Period,
    AP.AvgValue
FROM
    -- this groups by the period and gets the average
    (SELECT
        P.FiveMinutesPeriod,
        AVG(P.Value) AS AvgValue
    FROM
        -- This calculates the period (five minutes in this instance)
        (SELECT 
            -- note the division by 5 and the "minute" to build the 5 minute periods
            -- the '2010-01-01T00:00:00' is the starting point for the periods
            datediff(minute, '2010-01-01T00:00:00', T.Time)/5 AS FiveMinutesPeriod,
            T.Value
        FROM Test T) AS P
    GROUP BY P.FiveMinutesPeriod) AP

NOTE: I've divided this in 3 subqueries for clarity. You should read it from inside out. It could, of course, be written as a single, compact query

NOTE: if you change the period and the starting date-time you can get any interval you need, like weeks starting from a given day, or whatever you can need

If you want to generate test data for this query use this:

CREATE TABLE Test
( Id INT IDENTITY PRIMARY KEY,
Time DATETIME,
Value FLOAT)

INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:00:22', 10)
INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:03:22', 10)
INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:04:45', 10)
INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:07:21', 20)
INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:10:25', 30)
INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:11:22', 30)
INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:14:47', 30)

The result of executing the query is this:

Period                     AvgValue
2012-03-22 00:00:00.000    10
2012-03-22 00:05:00.000    20
2012-03-22 00:10:00.000    30

Answer 2

Building on @JotaBe's answer (to which I cannot comment on--otherwise I would), you could also try something like this which would not require a subquery.

 SELECT 
    AVG(value) AS 'AvgValue',

    -- Add the rounded seconds back onto epoch to get rounded time
    DATEADD(
        MINUTE,
        (DATEDIFF(MINUTE, '1990-01-01T00:00:00', your_date) / 30) * 30,
        '1990-01-01T00:00:00'
    )      AS 'TimeThirty'

 FROM YourTable
 -- WHERE your_date > some max lookback period
 GROUP BY
    (DATEDIFF(MINUTE, '1990-01-01T00:00:00', your_date) / 30)

This change removes temp tables and subqueries. It uses the same core logic for grouping by 30 minute intervals but, when presenting the data back as part of the result I'm just reversing the interval calculation to get the rounded date & time.

Answer 3

So, in case you googled this, but you need to do it in mysql, which was my case:

In MySQL you can do

GROUP BY
CONCAT(
    DATE_FORMAT(`timestamp`,'%m-%d-%Y %H:'),
    FLOOR(DATE_FORMAT(`timestamp`,'%i')/5)*5
)

Answer 4

In the new SQL Server 2022, you can use DATE_BUCKET, this rounds it down to the nearest interval specified.

SELECT
  DATE_BUCKET(minute, 5, d.sample_date) AS TimeFive,
  AVG(d.value) AS AvgValue  
FROM DATA d
WHERE d.id = 123
  AND d.sample_date >= '20121203'
GROUP BY
  DATE_BUCKET(minute, 5, d.sample_date);

Answer 5

You can use the following statement, this removed the second component and calculates the number of minutes away from the five minute mark and uses this to round down to the time block. This is ideal if you want to change your window, you can simply change the mod value.

select dateadd(minute, - datepart(minute,  [YOURDATE]) % 5, dateadd(minute, datediff(minute, 0, [YOURDATE]), 0)) as [TimeBlock]

Answer 6

This will help exactly what you want

replace dt - your datetime c - call field astro_transit1 - your table 300 refer 5 min so add 300 each time for time gap increase

SELECT FROM_UNIXTIME( 300 * ROUND( UNIX_TIMESTAMP( r.dt ) /300 ) ) AS 5datetime, ( SELECT r.c FROM astro_transit1 ra WHERE ra.dt = r.dt ORDER BY ra.dt DESC LIMIT 1 ) AS first_val FROM astro_transit1 r GROUP BY UNIX_TIMESTAMP( r.dt ) DIV 300 LIMIT 0 , 30