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

by

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

More Related Contents:

  1. How to delete 0 values but not blanks from a column in SQL Server 2008 programming?
  2. Unpivot with column name
  3. Optimal way to concatenate/aggregate strings
  4. How can I delete using INNER JOIN with SQL Server?
  5. Is there a way to access the “previous row” value in a SELECT statement?
  6. What this query does to create comma delimited list SQL Server?
  7. SQL Server – Return value after INSERT
  8. SQL NVARCHAR and VARCHAR Limits
  9. Convert varchar into datetime in SQL Server
  10. sql query to return differences between two tables
  11. Check if a row exists, otherwise insert
  12. T-SQL datetime rounded to nearest minute and nearest hours with using functions
  13. SQL Server : dynamic pivot over 5 columns
  14. How to select the last record of a table in SQL?
  15. How to create table using select query in SQL Server?
  16. Is there a SELECT … INTO OUTFILE equivalent in SQL Server Management Studio?
  17. SQL SERVER: Get total days between two dates
  18. Custom aggregate function (concat) in SQL Server
  19. Store multiple bit values in a single table column
  20. Which SQL query is faster? Filter on Join criteria or Where clause?
  21. SQL Server equivalent to MySQL enum data type?
  22. UPDATE and REPLACE part of a string
  23. Improve SQL Server query performance on large tables
  24. Using IF ELSE statement based on Count to execute different Insert statements
  25. Select SQL Server database size
  26. Backup a single table with its data from a database in sql server 2008
  27. COALESCE Function in TSQL
  28. Cannot Resolve Collation Conflict
  29. Try_Convert for SQL Server 2008 R2
  30. Display next event date

Leave a Comment