Get the ten minute intervals from a range of datetimes

Question

I have a datetime column with range of Datetime values. I want to create another column with all this datetime values but to round down to ten minute period.

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 I have got is playing around with is getting it in minute bucket 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 this:

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 lots of 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 gist of this is stripping out the number of minutes past a 10 minute interval and deducting this from the actual number of minutes (with seconds removed).

This can be tidied up a bit by moving some of the functions to a join. However, I don't think this offers any performance gain (not done any 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