Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server 2008 Generate a Series of date times

I am trying to generate a table with a series of date times in it.

I have the specified start date time and end date time(end date time is end of sequence), I add a time interval ~ (this can vary) to the start date time in seconds and this gives me the end date time.

The next sequence uses the end date time as its start value and adds the time interval in seconds to it. To demonstrate output I need. Is there a quick way to create such a table, other than using a lot of insert into commands?, I am really stumped

StartTime               Endtime                 Duration
2011-07-20 11:00:33     2011-07-20 11:09:47     554
2011-07-20 11:09:47     2011-07-20 11:19:01     554

    declare @StartTime datetime = '2011-07-20 11:00:33',
    @EndTime datetime = '2011-07-20 15:37:34'
    @Interval int = 554 -- this can be changed.

    insert into tmp_IRange
    values('2011-07-20 11:00:33', DATEADD(SECONDS, @Duration, 2011-07-20 11:00:33))

this becomes very tedious.. especially when the end date time is 2011-07-20 15:37:34 there are a lot of insert statements to make :(

like image 999
mouse Avatar asked Aug 22 '11 14:08

mouse


People also ask

How do you create a series of dates in SQL?

To generate a date sequence, you can use the number sequence technique and the T-SQL function DATEADD(). Suppose you need to generate a sequence of dates from 1/1/2001 to 3/1/2001. First, you generate the sequence of numbers from 0 to 59.

How do I select all dates between two dates in SQL Server?

DECLARE @MinDate DATE = '20140101', @MaxDate DATE = '20140106'; SELECT TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1) Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a. object_id) - 1, @MinDate) FROM sys.


1 Answers

Use a recursive CTE

declare @StartTime datetime = '2011-07-20 11:00:33',
    @EndTime datetime = '2011-07-20 15:37:34',
    @Interval int = 554 -- this can be changed.

;WITH cSequence AS
(
    SELECT
       @StartTime AS StartRange, 
       DATEADD(SECOND, @Interval, @StartTime) AS EndRange
    UNION ALL
    SELECT
      EndRange, 
      DATEADD(SECOND, @Interval, EndRange)
    FROM cSequence 
    WHERE DATEADD(SECOND, @Interval, EndRange) < @EndTime
)
 /* insert into tmp_IRange */
SELECT * FROM cSequence OPTION (MAXRECURSION 0);
like image 72
gbn Avatar answered Nov 16 '22 01:11

gbn