'SQL Server - Sample Timeseries
In PostgreSQL we use:
SELECT * FROM generate_series(1, 100)
SELECT * FROM generate_series('2020-01-01 00:00'::timestamp,'2020-01-02 00:00'::timestamp,'1 minutes')
How do we create the time-series in Microsoft SQL Server?
Solution 1:[1]
You may "translate" the statement in T-SQL using recursion:
DECLARE @datetime datetime2(0) = '2020-01-01 00:00:00'
DECLARE @start int = 1
DECLARE @stop int = 100
; WITH rCTE AS (
SELECT @start - 1 AS rn
UNION ALL
SELECT rn + 1 FROM rCTE WHERE rn < @stop
)
SELECT DATEADD(minute, rn, @datetime)
FROM rCTE
OPTION (MAXRECURSION 0)
Solution 2:[2]
SQL Server does not have a "simple" function. Instead Jeff Moden took a lot of ideas floating around and championed the tally table. Which in turn was picked up by others.
Like this code>
/*
Copyright Daniel Hutmacher under Creative Commons 4.0 license with attribution.
http://creativecommons.org/licenses/by/4.0/
Source: http://sqlsunday.com/downloads/
WHAT: Returns a gregorian calendar table for a specific interval of dates.
DISCLAIMER: This script may not be suitable to run in a production
environment. I cannot assume any responsibility regarding
the accuracy of the output information, performance
impacts on your server, or any other consequence. If
your juristiction does not allow for this kind of
waiver/disclaimer, or if you do not accept these terms,
you are NOT allowed to store, distribute or use this
code in any way.
USAGE: @from: Starting date
@to: Ending date
VERSION: 2018-07-17
*/
ALTER FUNCTION Calendar.Dates(@from date, @to date)
RETURNS TABLE
AS
RETURN (
--- Generic number table with 1000 rows:
WITH n(i) AS (
SELECT 0
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS x1(i)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS x2(i)
CROSS APPLY (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS x3(i)),
--- One row for each date between @from and @to.
--- Uncomment x3 if you need more than 1 million days. :)
n2(i) AS (
SELECT TOP (DATEDIFF(day, @from, @to)+1)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM n AS x1, n AS x2 -- , n AS x3, n AS x4
),
--- i is an incrementing integer, starting with 0 on @from.
dt(i, [date]) AS(
SELECT i, DATEADD(day, i, @from) AS [date]
FROM n2)
SELECT dt.i,
dt.[date] AS [Date],
CAST(YEAR(dt.[date]) AS smallint) AS [Year],
CAST(DATEPART(quarter, dt.[date]) AS tinyint) AS [Quarter],
CAST(CAST(YEAR(dt.[date]) AS char(4))+' Q'+CAST(DATEPART(quarter, dt.[date]) AS char(1)) AS char(7)) AS Year_quarter,
CAST(MONTH(dt.[date]) AS tinyint) AS [Month],
CAST(CAST(YEAR(dt.[date]) AS char(4))+'-'+REPLACE(STR(MONTH(dt.[date]), 2, 0), ' ', '0') AS char(7)) AS Year_month,
CAST(DAY(dt.[date]) AS tinyint) AS [Day],
CONVERT(char(10), dt.[date], 121) AS Date_ISO,
CAST(dt1.iso_week_year AS smallint) AS ISO_week_year,
CAST(DATEPART(isowk, dt.[date]) AS tinyint) AS [ISO_week],
CAST(CAST(dt1.iso_week_year AS varchar(4))+' W'+REPLACE(STR(DATEPART(isowk, dt.[date]), 2, 0), ' ', '0') AS char(8)) AS ISO_year_week,
CAST(dt1.weekday_iso AS tinyint) AS ISO_weekday_number,
CAST(DATEPART(week, dt.[date]) AS tinyint) AS US_week,
CAST(CAST(YEAR(dt.[date]) AS varchar(4))+' W'+REPLACE(STR(DATEPART(week, dt.[date]), 2, 0), ' ', '0') AS char(8)) AS US_year_week,
CAST(dt1.weekday_us AS tinyint) AS US_weekday_number,
DATENAME(weekday, dt.[date]) AS Weekday_name,
CAST(DATEPART(dy, dt.[date]) AS smallint) AS Day_of_year,
30*(MONTH(dt.[date])-1)+(CASE WHEN dt.[date]=EOMONTH(dt.[date]) THEN 30 ELSE DAY(dt.[date]) END) AS Day_of_year_30E_360
FROM dt
--- Calculation steps:
CROSS APPLY (
VALUES (
1+(DATEPART(dw, dt.[date])+@@DATEFIRST-2)%7,
1+(DATEPART(dw, dt.[date])+@@DATEFIRST-1)%7,
YEAR(dt.[date])+(CASE WHEN DATEPART(isowk, dt.[date])>50 AND DATEPART(dy, dt.[date])<7 THEN -1
WHEN DATEPART(isowk, dt.[date])=1 AND DATEPART(dy, dt.[date])>300 THEN 1
ELSE 0 END)
)) AS dt1(weekday_iso, weekday_us, iso_week_year)
)
GO
It uses at its core a numbers table to generate dates and other attributes. This can be changed to times if needed.
Solution 3:[3]
This solves the problem of generating per/minute dates and times for a given range of dates. The original concept was provided by Itzik Ben-Gan more than a decade ago. It could be turned into a function like @KnutBoehnert did. Details are in the comments. Note that the limits of the number of date parts for DATEADD is limited to the positive limits of an INT, which is 2,147,483,647 minutes for this problem which translates a date/time of 5983-01-24 02:07:00.000 when that number of minutes is added to 1900-01-01 00:00:00.
DECLARE @LoDT DATETIME2(0) = '2020-01-01 00:00' --Inclusive
,@HiDT DATETIME2(0) = '2020-01-02 00:00' --Inclusive
;
WITH
--====== Create up to 16 rows of one's as the initial row source for the Tally "Pseudo-Cursor"
E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))E0(N)),
--====== Create one numbered row per second in the desired date range starting at 0.
-- I call this a "Pseudo-Cursor" because it's a "set based" way to count without using an explicit loop
-- or a recursive CTE which is even slower and more resource intensive that a properly built WHILE loop.
Tally(N) AS (SELECT N = 0 UNION ALL --Starts with "0"
SELECT TOP(DATEDIFF(mi,@LoDT,@HiDT)) --Controls the number of minute rows to be created
N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) --Number of minutes to add to @LoDT for each row.
FROM E1 a,E1 b,E1 c,E1 d,E1 e,E1 f,E1 g,E1 h) --Up to 16^8 or 4,294,967,296 rows thanks to CROSSJOIN.
--===== Create the final date squence by adding the minute rows created above to @LoDT
-- to create the date/time sequence.
SELECT DateSeq = DATEADD(mi,t.N,@LoDT)
FROM Tally t
ORDER BY t.N
;
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Zhorov |
| Solution 2 | Dale K |
| Solution 3 | Jeff Moden |