SQL-Quick tip #5 - Create a sequence of date and time

Sql Server tips and tricks

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.

Create a sequence of date and time

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
        UNION ALL
       SELECT DATEADD(MINUTE, @stepSizeInMinutes, TimestampUtc) 
         FROM Recursive_CTE
        WHERE TimestampUtc < @to
)
SELECT *
  FROM Recursive_CTE
 ORDER BY TimestampUtc
OPTION (MAXRECURSION 0);