Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a datetime column and I can easily run queries with a group by on my datetime column. However, I would like to run group queries for

  • 1-minute intervals
  • 2-minute intervals
  • 5-minute intervals
  • 1 hour intervals
  • etc.

How do I do this?

share|improve this question
1  
All in the same query, or do you want the query to conditionally group by a specified interval? If the former, could you show sample data and desired results? –  Aaron Bertrand May 16 '12 at 16:43
    
Each group by interval would be in a different query. I was just picking those intervals as examples. Idea is I could rerun query and easily adjust some clause to get 2 mins, 2 mins, 5 mins etc. –  dublintech May 16 '12 at 16:45
1  
Have a look at this answer. dba.stackexchange.com/questions/17669/… –  Mikael Eriksson May 16 '12 at 17:40

3 Answers 3

up vote 7 down vote accepted

First, a table and some sample data to play with:

USE tempdb;
GO
CREATE TABLE dbo.SomeTable(dt DATETIME);
GO
SET NOCOUNT ON;
GO
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -22, GETDATE());
GO 45
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -19, GETDATE());
GO 32
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -17, GETDATE());
GO 21
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -12, GETDATE());
GO 16
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -5, GETDATE());
GO 55
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -2, GETDATE());
GO 26
INSERT dbo.SomeTable(dt) SELECT DATEADD(MINUTE, -1, GETDATE());
GO 71
INSERT dbo.SomeTable(dt) SELECT GETDATE();
GO 14

(I'd do this in sqlfiddle but I'm not sure it supports GO <int> to have many rows, and it chokes on INSERT > 8000 characters.)

Now a stored procedure:

CREATE PROCEDURE dbo.GetGroupedIntervals
    @MinuteInterval TINYINT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE 
        @IntervalCount INT, @StartDate SMALLDATETIME;

    SELECT
        @StartDate = DATEADD(MINUTE, -1, MIN(dt)), 
        @IntervalCount = (DATEDIFF(MINUTE, MIN(dt), MAX(dt)) 
            + @MinuteInterval) / @MinuteInterval
          FROM dbo.SomeTable -- WHERE ...;

    ;WITH dates(s,e) AS
    (
        SELECT 
            DATEADD(MINUTE, @MinuteInterval*(n.n-1), @StartDate),
            DATEADD(MINUTE, @MinuteInterval*(n.n), @StartDate)
        FROM
        (
          SELECT 
            TOP (@IntervalCount) ROW_NUMBER() OVER (ORDER BY o.[object_id])
            FROM sys.all_objects AS o CROSS JOIN sys.all_columns AS c
            ORDER BY o.[object_id]
        ) AS n(n)
    )
    SELECT StartDate = d.s, c = COUNT(s.dt) 
    FROM dates AS d
    LEFT OUTER JOIN dbo.SomeTable AS s
        ON s.dt >= d.s AND s.dt < d.e
        -- AND any filter criteria for dbo.SomeTable?
    GROUP BY d.s
    ORDER BY d.s;
END
GO

And some sample usage:

EXEC dbo.GetGroupedIntervals @MinuteInterval = 1;
EXEC dbo.GetGroupedIntervals @MinuteInterval = 2;
EXEC dbo.GetGroupedIntervals @MinuteInterval = 5;

For brevity, I'll show the results for the last call, but you can play with the others.

StartDate            c
-------------------  ----
2012-05-16 12:51:00  77
2012-05-16 12:56:00  21
2012-05-16 13:01:00  16
2012-05-16 13:06:00  55
2012-05-16 13:11:00  111

Some notes:

  • The join using s.dt is likely to perform better than any extraction methods using datepart if the datetime column in your base table has an index (or might in the future).
  • I assumed you wanted to show all the intervals within the range. If you don't want to show the intervals with 0 counts, just change the left outer join to an inner join.
  • I go down a minute in case the start date was rounded up when converted to SMALLDATETIME. For the 1 minute interval and possibly others, this can lead to 0 counts for the first interval. You can adjust how this rounding happens (e.g. you can use FLOOR() to make sure it always rounds down). All depends on how accurate you need to be.
  • I didn't include any WHERE clause but you may need to have those to filter. For example your query may want all the intervals for a given day. You may want to change the calculation of dates to produce all the intervals for the day, rather than all the intervals between the min and max time found in dbo.SomeTable on that day. The following variation takes care of this, by presenting the data for a single day starting from midnight and incrementing by @MinutInterval:

...

CREATE PROCEDURE dbo.GetGroupedIntervalsByDay
    @Date           DATE,
    @MinuteInterval TINYINT = 1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE 
        @IntervalCount INT,
        @StartDate     SMALLDATETIME = @Date;

    SELECT
        @IntervalCount = 1440 / @MinuteInterval;

    ;WITH dates(s,e) AS
    (
        SELECT 
            DATEADD(MINUTE, @MinuteInterval*(n.n-1), @StartDate),
            DATEADD(MINUTE, @MinuteInterval*(n.n),   @StartDate)
        FROM
        (
          SELECT 
            TOP (@IntervalCount) ROW_NUMBER() OVER (ORDER BY o.[object_id])
            FROM sys.all_columns AS o
            ORDER BY o.[object_id]
        ) AS n(n)
    )
    SELECT StartDate = d.s, c = COUNT(s.dt) 
    FROM dates AS d
    LEFT OUTER JOIN dbo.SomeTable AS s
        ON s.dt >= d.s AND s.dt < d.e
        -- AND any filter criteria for dbo.SomeTable?
    GROUP BY d.s
    ORDER BY d.s;
END
GO

Sample calls:

EXEC dbo.GetGroupedIntervalsByDay @Date = '20120516', @MinuteInterval = 1;
EXEC dbo.GetGroupedIntervalsByDay @Date = '20120516', @MinuteInterval = 2;
EXEC dbo.GetGroupedIntervalsByDay @Date = '20120516', @MinuteInterval = 5;

Abridged results from the last call:

StartDate             c
-------------------   ----
2012-05-16 00:00:00   0
2012-05-16 00:05:00   0
2012-05-16 00:10:00   0
...
2012-05-16 12:40:00   0
2012-05-16 12:45:00   0
2012-05-16 12:50:00   45
2012-05-16 12:55:00   53
2012-05-16 13:00:00   16
2012-05-16 13:05:00   55
2012-05-16 13:10:00   111
2012-05-16 13:15:00   0
2012-05-16 13:20:00   0
...
2012-05-16 23:45:00   0
2012-05-16 23:50:00   0
2012-05-16 23:55:00   0

(Again if you don't want to include the intervals with no counts, change the left outer join to an inner join. You also might find some odd results if you choose an interval that doesn't fit nicely inside of 1440. I'll leave that case as an exercise to the reader.)

share|improve this answer
    
Thanks for the heads up regarding GO <int> - I didn't even know that was an option for GO. I'll add it to the list of enhancements for SQL Fiddle (my site). –  Jake Feasel May 20 '12 at 4:53
declare @T table
(
  Value datetime
);

insert into @T values ('2012-01-01T00:02:00');
insert into @T values ('2012-01-01T00:03:00');
insert into @T values ('2012-01-01T00:04:00');
insert into @T values ('2012-01-01T00:05:00');
insert into @T values ('2012-01-01T00:06:00');
insert into @T values ('2012-01-01T00:07:00');
insert into @T values ('2012-01-01T00:08:00');
insert into @T values ('2012-01-01T00:09:00');
insert into @T values ('2012-01-01T00:10:00');
insert into @T values ('2012-01-01T00:11:00');

-- Interval in minutes    
declare @Interval int
set @Interval = 4

select count(*) as "Count",
       dateadd(minute, (datediff(minute, 0, Value) / @Interval) * @Interval, 0) as "Value"
from @T
group by dateadd(minute, (datediff(minute, 0, Value) / @Interval) * @Interval, 0);

Result:

Count       Value
----------- -----------------------
2           2012-01-01 00:00:00.000
4           2012-01-01 00:04:00.000
4           2012-01-01 00:08:00.000
share|improve this answer

Since DATEPART returns integers...

SELECT
    (DATEPART(MINUTE, StartDate) / 5) * 5, COUNT(*)
FROM
    Table
GROUP BY
    (DATEPART(MINUTE, StartDate) / 5) * 5
ORDER BY
    (DATEPART(MINUTE, StartDate) / 5) * 5

The numbers shown are the start of the five-minute period. So the row labeled "30" covers events happening at X:30, X:31, X:32, X:33, and X:34.

share|improve this answer
    
Why are you multiplying and dividing by 5. Do they cancel each other out? –  dublintech May 16 '12 at 16:48
1  
Yes, but since it does not preserve fractions the net result is to round down to the nearest multiple of five. –  Jon of All Trades May 16 '12 at 16:50
2  
Won't this group together across hours? 4:30 will be combined with 5:30 and 6:30 since the minute is the same across the board. You need to include date and hour as well at a minimum. –  JNK May 16 '12 at 17:23
    
If that's part of the OP's goal, then certainly yes. This query meets the criteria "group queries for X minute intervals." Expand upon it as necessary. –  Jon of All Trades May 16 '12 at 19:12
    
Sure, if the where clause limits all of the data to within a single hour, it meets the criteria. I don't think that's explicit enough in either the question or your answer. IMHO. –  Aaron Bertrand May 16 '12 at 19:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.