MS SQL - Sample Timeseries-CodePudding

In PostreSQL 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 generate time-series in Microsoft SQL?

CodePudding user response：

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)

CodePudding user response：

MS SQL 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.