This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.
When working with data that is somehow related to dates and times you may need to generate a sequence of date to group by or select from.
The trick here is to use a recursive CTE (Common Table Expression). It is a bit tricky to read but what the CTE does is to make a union with one additional datetime for each recursion.
The code below will create a sequence of datetimes with 10 minutes interval between to dates.
Like recursion in general this can take up a lot of resources and be slow so I would not recommend using it in production code with high number of execution. But for data analysis and drilling into your data this trick can be very useful.
DECLARE @stepSizeInMinutes INT = 10; -- Change this line to change the time interval
DECLARE @from DATETIME2 = '2017-01-01 00:00:00.00000',
@to DATETIME2 = '2017-01-04 23:50:00.00000';
-- Create Recursive Discrete Table
WITH Recursive_CTE AS
SELECT @from AS TimestampUtc
SELECT DATEADD(MINUTE, @stepSizeInMinutes, TimestampUtc)
WHERE TimestampUtc < @to
ORDER BY TimestampUtc
OPTION (MAXRECURSION 0);