sql - 如何在 Azure SQL 中迭代增加 1 秒的日期之间的日期时间列表

Question

我尝试在 SQL Server 中生成一个日期时间列表，向 start_date 列添加 1 秒，并与 stateDuration 列的值相同，例如，如果 stateDuration 为 100，start_date 为“2022-09-29 07:29:16.000”，那么新列(我们称之为 new_time)应该从相同的日期时间开始，循环 100 行，增加 1 秒。然后将 new_time 舍入到最接近的 900 秒，然后将数据分组，如下所示。

原始数据如下:

userid  statuskey   stateDuration   StatusDateTimeGMT   EndDateTimeGMT
Abanoub.Sanad   available   4656    2022-09-29 07:29:16 2022-09-29 08:46:52
Abanoub.Sanad   available   24626   2022-09-29 10:12:26 2022-09-29 17:02:52
Abanoub.Sanad   available   9030    2022-09-29 17:18:23 2022-09-29 19:48:53
Abanoub.Sanad   available   33647   2022-09-29 23:04:07 2022-09-30 08:24:54

以第一行的预期数据为例:

同一逻辑上sql和excel查询的对比

使用的查询

with cte1 as(
select userid,statuskey,stateDuration,[StatusDateTimeGMT]+ '02:00' as StatusDateTimeGMT,[EndDateTimeGMT] + '02:00' as EndDateTimeGMT,
interval= cast(floor(cast([StatusDateTimeGMT]+ '02:00' as float)*(96))/(96) as datetime),
interval_end_date=cast(ceiling(cast([StatusDateTimeGMT]+ '02:00' as float)*(96))/(96) as datetime)
from AgentActivityLog
where [StatusDateTimeGMT]+'2:00'>= '2022-09-28' and [StatusDateTimeGMT]+'2:00' < '2022-09-30' and StateDuration > 0 and userid = 'Abanoub.Sanad'),
cte2 as(
select userid,statuskey,[EndDateTimeGMT],[StatusDateTimeGMT],interval,interval_end_date from cte1
union all
select userid,statuskey,[EndDateTimeGMT],interval_end_date,dateadd(second,900,interval),dateadd(second,900,interval_end_date) 
from cte2 
where dateadd(second,15,interval_end_date)< [EndDateTimeGMT])
select userid,statuskey,interval,
[Duration]= case 
when interval_end_date<[EndDateTimeGMT] then datediff(second,[StatusDateTimeGMT],interval_end_date)
else datediff(second,[StatusDateTimeGMT],[EndDateTimeGMT]) end
from cte2
ORDER BY interval

Answer 1

输入表采用一条记录，如下图所示

SQL 脚本

CREATE TABLE [dbo].[src_table](
    [UserId] [varchar](100) NULL,
    [StatusKey] [varchar](100) NULL,
    [StateDuration] [int] NULL,
    [Start_date] [datetime] NULL
) ;
 
INSERT INTO SRC_TABLE VALUES('AAA','available','9030','2022-09-29 17:18:23'); 

    with cte1 as(
    select userid,statuskey,stateDuration,[start_date],
    nearestdate= DATEADD( minute, ( DATEDIFF( minute, 0,start_date ) / 15 ) * 15, 0 ),
    batch_end_date=DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( minute,15, start_date ) ) / 15 ) * 15, 0 ),
    total_end_date=DATEADD(second,stateDuration,[start_date])
    from src_table
    ),
    cte2 as(
    select userid,statuskey,total_end_date,[start_date],nearestdate,batch_end_date from cte1
    union all
    select userid,statuskey,total_end_date,batch_end_date,dateadd(second,900,nearestdate),
    dateadd(second,900,batch_end_date) from cte2 where dateadd(second,15,batch_end_date)< total_end_date)
    select userid,statuskey,nearestdate,
    [count]= case 
    when batch_end_date<total_end_date then datediff(second,Start_date,batch_end_date)
    else datediff(second,Start_date,total_end_date) end
    from cte2

输出