SQL Server 2008 Generate a Series of date times

Question

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 :(

You didn't really demonstrate your output. Can you show the first three and last three rows you actually want? — Aaron Bertrand, Aug 22 '11 at 15:02
Note that the answers based on recursive CTEs have a limit of 32767 levels of recursion. (See MAXRECURSION documentation.) — HABO, Aug 22 '11 at 19:56
@user92546 - That is not the case. `MAXRECURSION 0` is unlimited. — Martin Smith, Aug 23 '11 at 05:55
@Martin - Thanks. When I read up on MAXRECURSION some time ago the limit of 32767 stuck in my tiny brain. Rereading it, it clearly says zero means unlimited. — HABO, Sep 09 '11 at 01:04

score 19 · Answer 1 · edited Apr 17 '14 at 20:21

19

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);

edited Apr 17 '14 at 20:21

Dan Esparza

28,047
29
99
127

answered Aug 22 '11 at 14:58

gbn

422,506
82
585
676

Is Interval used in this query, there seems to be undeclared variable Duration in this query. – jrara Aug 22 '11 at 18:26

Aaron Bertrand · Answer 2 · 2011-08-22T15:10:31.317

5

This one will give the individual ranges but will ignore your actual end time (since it is < @interval after the last valid range):

;WITH x AS 
(
    SELECT TOP (DATEDIFF(SECOND, @StartTime, @EndTime)/@Interval) 
        rn = ROW_NUMBER() OVER (ORDER BY [object_id])
    FROM sys.objects
)
-- INSERT INTO dbo.tmp_IRange
SELECT DATEADD(SECOND, @Interval * (rn-1), @StartTime),
    DATEADD(SECOND, @Interval * rn, @StartTime)
FROM x;

edited Aug 22 '11 at 15:10

answered Aug 22 '11 at 14:59

Aaron Bertrand

272,866
37
466
490

*'The next sequence uses the End date time as its start value'*, which means the start time is not a fixed value. – Andriy M Aug 22 '11 at 15:06
Thanks @Andriy, missed that; deleted first answer. – Aaron Bertrand Aug 22 '11 at 15:10

score 4 · Answer 3 · answered Aug 22 '11 at 14:56

4

This should get you started. You can adapt it to your specific needs. As written it will generate a row for each minute increment starting with the current date & time.

DECLARE @BaseDate DateTime = GETDATE();

WITH DateTable (DateValue) AS (
    SELECT @BaseDate DateValue
    UNION ALL
    SELECT DATEADD(Minute, 1, DateValue) DateValue
    FROM DateTable
)
SELECT *
FROM DateTable
WHERE DateValue < DATEADD(Day, 1, GETDATE())
OPTION (MAXRECURSION 0);

answered Aug 22 '11 at 14:56

Yuck

49,664
13
105
135

@JNK: Yes, edited the description and increments. As it was, the query would run for quite a while! – Yuck Aug 22 '11 at 14:59
Well you only have a single datetime variable whereas the OP has two datetime variables and an interval. Why is there nothing in your solution about the interval (554 seconds) and the end date (you're assuming it's a 1-minute interval across a single day)? – Aaron Bertrand Aug 22 '11 at 15:05

score 4 · Answer 4 · edited May 23 '17 at 10:31

Here's another non-recursive set-based solution, which uses a system table called master..spt_values:

DECLARE
  @StartTime datetime = '2011-07-20 11:00:33',
  @EndTime datetime = '2011-07-20 15:37:34',
  @Interval int = 554;
SELECT
  StartTime = DATEADD(SECOND, (number - 1) * @Interval, @StartTime),
  EndTime   = DATEADD(SECOND, (number - 0) * @Interval, @StartTime),
  Duration  = @Interval
FROM master..spt_values
WHERE type = 'P'
  AND number BETWEEN 1 AND DATEDIFF(SECOND, @StartTime, @Endtime) / @Interval

UNION ALL

SELECT
  DATEADD(SECOND, -Duration, EndTime),
  EndTime,
  Duration
FROM (
  SELECT
    EndTime = @EndTime,
    Duration = DATEDIFF(SECOND, @StartTime, @Endtime) % @Interval
) s
WHERE Duration > 0

The first SELECT generates a row set consisting of short intervals of specified length that lie within the specified range. If necessary, the second SELECT adds an interval between the first SELECT's last interval's end time and the specified end time.

The subset of master..spt_values that is particularly used here (and can be used in many similar cases) provides a list of numbers from 0 to 2047. This means for you that you will not be able to use this solution with that table if the initial interval is going to be split into more that 2047 short(er) intervals. You should then think of something like your own number table.

+1 but please call it a number or numbers table, not a tally table. It is a table of numbers, not a table of sums. :-) — Aaron Bertrand, Aug 22 '11 at 16:34
No problem! I thought the two terms were completely interchangeable. Although I admit, I can't fully understand all the subtleties of the meaning of the word *tally* (and it follows that I should have been more cautious in my prior usages of the term *tally table*). Thank you very much for the notice! — Andriy M, Aug 22 '11 at 17:28
Jeff Moden is trying to "reinvent" the numbers table idea (which did not originate from him) by renaming it. So discussions evolve where two people are using different terms, and it can be confusing. The table of numbers *can* be used for tallying, but that is only a very specific use case, even if it is one of the more common ones. Anyway, this is just my opinion, totally subjective, but I try to shoot down the proliferation of the term "tally table" when I think people might listen. :-) — Aaron Bertrand, Aug 22 '11 at 17:34

score 0 · Answer 5 · answered Aug 22 '11 at 15:07

Hope this helps...

declare @StartTime datetime = '2011-07-20 11:00:33',
@EndTime datetime = '2011-07-20 11:00:33',
@Interval int = 554,

@LimitTime datetime = '2011-07-20 15:37:34'

WHILE @EndTime < @LimitTime
BEGIN
SELECT @EndTime = DATEADD(S, @Interval, @StartTime)

SELECT @StartTime, @EndTime
--INSERT INTO tmp_IRange VALUES(@StartTime, @EndTime)

SELECT @StartTime = @EndTime

END

score 0 · Answer 6 · answered Aug 22 '11 at 15:09

please try this code:

create table #T (date_begin datetime, date_end datetime)    

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

while DATEADD(ss,@Interval,@StartTime)<=@EndTime
begin
    insert #T
    select @StartTime, DATEADD(ss,@Interval,@StartTime)

    set @StartTime = DATEADD(ss,@Interval,@StartTime)
end


select * from #T

SQL Server 2008 Generate a Series of date times

6 Answers6

Linked

Related