SQL Server 2008生成一系列日期时间 [英] SQL Server 2008 Generate a Series of date times

问题描述

我有指定的开始日期时间和结束日期时间（结束日期时间为序列结束），我添加一个时间间隔〜（这可以变化）到开始日期时间（以秒为单位），这给我结束日期时间。

下一个序列使用结束日期时间作为其起始值，并以秒为单位添加时间间隔。为了演示我需要的输出。有没有一个快速的方法来创建这样一个表，除了使用大量的插入命令？我真的很难受

  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  - 这可以改变。 
 
插入到tmp_IRange 
值（'2011-07-20 11:00:33'，DATEADD（SECONDS，@Duration，2011-07-20 11:00:33））
  

这变得非常繁琐..特别是当结束日期时间是 2011-07 -20 15:37:34
有很多insert语句可以做：（

解决方案

使用递归CTE

  declare @StartTime datetime ='2011-07-20 11:00:33' 
 @EndTime datetime ='2011-07-20 15:37:34'，
 @Interval int = 554  - 可以更改
 
; 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进入tmp_IRange * / 
 SELECT * FROM cSequen选项（MAXRECURATION 0）; 
  

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

解决方案

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

这篇关于SQL Server 2008生成一系列日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！