Get Ten Minute Intervals from a Date Range

Question

I have a datetime column with a range of Datetime values. I want to create another column with all these datetime values, but round to ten minutes.

So something like this:

datetimesent | ten_minute_column 2012-06-11 18:27:58.000 | 2012-06-11 18:20:00.000 2012-06-15 15:19:08.000 | 2012-06-15 15:10:00.000 ... | 

The farthest thing I have is to play with him, getting him in small slots. I got this by doing:

 SELECT DatetimeSent, DATEADD(Minute, DATEDIFF(Minute, 0, DatetimeSent), 0) AS Minute_bucket FROM allrequests 

But I need ten minute bucket slots.

Answer 1

Try the following:

 select dateadd(minute, datepart(minute, datetimesent) / 10 * 10, dateadd(hour, datediff(hour, 0,datetimesent), 0)) ten_minute_column from (select cast('2012-06-11 18:27:58.000' as datetime) datetimesent union all select cast('2012-06-15 15:19:08.000' as datetime)) a 

Answer 2

You can do this using many functions:

 WITH D AS ( SELECT CURRENT_TIMESTAMP [DateField] UNION ALL SELECT DATEADD(MINUTE, 5, CURRENT_TIMESTAMP) ) SELECT DATEADD(MINUTE, (10 * FLOOR(DATEPART(MINUTE, DateField) / 10.0)) - DATEPART(MINUTE, DateField), DATEADD(MINUTE, DATEDIFF(MINUTE, 0, DateField), 0)) AS RoundedDate FROM D 

The bottom line is to remove the number of minutes in a 10-minute interval and subtract this from the actual number of minutes (with the removal of seconds).

You can pick this up a bit by moving some of the functions to the connection. However, I do not think that this gives any performance gain (no testing at all)

 ;WITH T AS ( SELECT Number, (10 * FLOOR(Number / 10.0)) - Number [RoundedDifference] FROM ( SELECT ROW_NUMBER() OVER(ORDER BY Object_ID) - 1 [Number] FROM sys.All_Objects ) n WHERE Number < 60 ), D AS ( SELECT CURRENT_TIMESTAMP [DateField] UNION ALL SELECT DATEADD(MINUTE, 5, CURRENT_TIMESTAMP) ) SELECT DateField, DATEADD(MINUTE, RoundedDifference, DATEADD(MINUTE, DATEDIFF(MINUTE, 0, DateField), 0)) [RoundedDate] FROM D INNER JOIN T ON DATEPART(MINUTE, DateField) = Number 

Answer 3

Assuming milliseconds never exist, you can remove minutes and seconds this way and group by result:

 SELECT DATEADD(SECOND, -(CONVERT(INT, RIGHT(CONVERT(CHAR(2), DATEPART(MINUTE, GETDATE())),1))*60)-(DATEPART(SECOND,GETDATE())), GETDATE()); 

Here is a query that receives the correct number of time intervals based on the minimum and maximum dates from the table (or a subset of the table):

 DECLARE @x TABLE(datetimesent DATETIME); INSERT @x SELECT '2012-06-11 18:27:58.000' UNION ALL SELECT '2012-06-15 15:19:08.000'; DECLARE @start SMALLDATETIME, @end SMALLDATETIME, @i INT; SELECT @start = CONVERT(DATE, MIN(datetimesent)), @end = CONVERT(DATE, MAX(datetimesent)) FROM @x -- WHERE ...; SELECT @i = DATEDIFF(DAY, @start, @end) * 144; ;WITH slots(ten_minute_column) AS ( SELECT TOP (@i * 144) DATEADD(MINUTE, 10 * (ROW_NUMBER() OVER (ORDER BY s1.[object_id])-1), @start) FROM sys.all_columns AS s1 -- you may need to cross join to another table if this doesn't -- provide enough rows. Depends on overall datediff... ) SELECT x.datetimesent, slots.ten_minute_column FROM @x AS x INNER JOIN slots ON x.datetimesent >= slots.ten_minute_column AND x.datetimesent < DATEADD(MINUTE, 10, slots.ten_minute_column) -- WHERE ...; 

Results:

 datetimesent ten_minute_column ----------------------- ------------------- 2012-06-11 18:27:58.000 2012-06-11 18:20:00 2012-06-15 15:19:08.000 2012-06-15 15:10:00 

Answer 4

 SELECT DatetimeSent, Dateadd(ms, -Datepart(ms, Dateadd(minute, Datediff(minute, 0, Dateadd(minute, -Datepart(minute, datetimesent) %10, datetimesent)), 0 ) ), Dateadd(minute, Datediff(minute, 0, Dateadd(minute, - Datepart( minute, datetimesent )% 10, datetimesent )), 0)) AS Minute_bucket FROM allrequests