SQL Server’s GENERATE_SERIES() function returns a series of numbers within a given interval. But just because it returns numbers, doesn’t mean we can’t leverage its functionality in order to generate a series of time values.
If you need to create a series of time values with evenly spaced intervals between a start and end point, maybe the following technique can help.
Example
Here’s a simple example of how we can use SQL Server’s GENERATE_SERIES() function/relational operator in conjunction with the DATEADD() function to generate a time series:
SELECT
DATEADD(minute, value, '2024-10-02 00:00:00.000') AS "Time Series"
FROM GENERATE_SERIES(0, 5, 1);Result:
Time Series ----------------------- 2024-10-02 00:00:00.000 2024-10-02 00:01:00.000 2024-10-02 00:02:00.000 2024-10-02 00:03:00.000 2024-10-02 00:04:00.000 2024-10-02 00:05:00.000
In this case I created a time series based on a string literal datetime value. The DATEADD() function is what actually modifies the datetime value, whereas the GENERATE_SERIES() relational operator is used to define the actual series for which the datetime value will be incremented in accordance with (i.e. start point, stop point, step amount).
Just to be clear, let’s add a column that shows what’s returned by GENERATE_SERIES() itself:
SELECT
value,
DATEADD(minute, value, '2024-10-02 00:00:00.000') AS "Time Series"
FROM GENERATE_SERIES(0, 5, 1);Result:
value Time Series
----------- -----------------------
0 2024-10-02 00:00:00.000
1 2024-10-02 00:01:00.000
2 2024-10-02 00:02:00.000
3 2024-10-02 00:03:00.000
4 2024-10-02 00:04:00.000
5 2024-10-02 00:05:00.000
The GENERATE_SERIES() function returns its result in a single-column called value. Therefore, we can use value to show us the output of the GENERATE_SERIES(). This in turn allows us to see the value in which we’re incrementing the current time value by.
Convert to a time Value
The above example returns a datetime value. The function does this whenever we pass a string literal date/time value. If we don’t want the date portion, we can use CONVERT() or CAST() to convert the result:
SELECT
CONVERT(
time(0),
DATEADD(hour, value, '2024-10-02 00:00:00.000')
) AS "Time Series"
FROM GENERATE_SERIES(0, 5, 1);Result:
Time Series
----------------------
00:00:00
01:00:00
02:00:00
03:00:00
04:00:00
05:00:00
In this case I incremented the hour portion and converted to a time(0) value.
If we pass an actual date/time data type, then its return value uses that type:
DECLARE @time time = '00:00:00';
SELECT
value,
DATEADD(second, value, @time) AS Result
FROM GENERATE_SERIES(0, 70, 10);Result:
value Result
----------- ----------------------
0 00:00:00.0000000
10 00:00:10.0000000
20 00:00:20.0000000
30 00:00:30.0000000
40 00:00:40.0000000
50 00:00:50.0000000
60 00:01:00.0000000
70 00:01:10.0000000
Therefore, there’s no need to convert the values if they’re already in the required format.
In this example, the original value is of a time type, which is the equivalent of time(7).
The increments in our time series can go right down to the nanosecond if required:
DECLARE @time time = '00:00:00';
SELECT
value,
DATEADD(ns, value, @time) AS Result
FROM GENERATE_SERIES(0, 3500, 700);Result:
value Result
----------- ----------------------
0 00:00:00.0000000
700 00:00:00.0000007
1400 00:00:00.0000014
2100 00:00:00.0000021
2800 00:00:00.0000028
3500 00:00:00.0000035
Create a Decrementing time Series
We can use negative values to create a decrementing time series.
Example:
SELECT
value,
DATEADD(hour, value, '2024-10-02 00:00:00.000') AS "Time Series"
FROM GENERATE_SERIES(5, -5, -1);Result:
value Time Series
----------- -----------------------
5 2024-10-02 05:00:00.000
4 2024-10-02 04:00:00.000
3 2024-10-02 03:00:00.000
2 2024-10-02 02:00:00.000
1 2024-10-02 01:00:00.000
0 2024-10-02 00:00:00.000
-1 2024-10-01 23:00:00.000
-2 2024-10-01 22:00:00.000
-3 2024-10-01 21:00:00.000
-4 2024-10-01 20:00:00.000
-5 2024-10-01 19:00:00.000