如何在 T-SQL 中生成两个日期之间的分钟间隔? [英] How to generate minute intervals between two dates in T-SQL?

问题描述

我有一张开始时间和结束时间的表格.我需要以分钟为单位生成这两个日期之间的间隔表.以下是一些示例数据:

I have a table of startTime and endTimes. I need to generate a table of intervals between those two dates in minutes. Here's some sample data:

declare @intervalMinutes int = 10
declare @myDates table (
myId int primary key identity,
startTime datetime,
endTime datetime
)

insert @myDates (startTime, EndTime) values ('2016-07-10 08:00','2016-07-10 09:00')
insert @myDates (startTime, EndTime) values ('2016-07-12 10:00','2016-07-12 12:00')
insert @myDates (startTime, EndTime) values ('2016-07-14 12:30','2016-07-14 14:30')

我想看到的是，对于每个 myId，一组日期间隔 @intervalMinutes.

What I'd like to see is for each myId a set of dates of interval @intervalMinutes.

因此，如果我们将 @intervalMinutes 设置为 10，那么我会在第一行看到 2016-07-10 08:00 和2016-07-10 09:00 以 10 分钟为增量.

So if we had @intervalMinutes set to 10 then I'd see for the first row a list of 6 dates between 2016-07-10 08:00 and 2016-07-10 09:00 in 10 minute increments.

推荐答案

数字表可以解决您的问题.假设您不需要超过几千行，那么这应该可行:

A numbers table can solve your problem. Assuming you don't need more than a few thousand rows, then this should work:

with n as (
      select row_number() over (order by (select null)) - 1 as n
      from master.spt_values
     )
select d.*,
       dateadd(minute, n.n * @intervalMinutes, d.startTime)
from @myDates d join
     n
     on dateadd(minute, n.n * @intervalMinutes, d.startTime) <= d.endTime;

这篇关于如何在 T-SQL 中生成两个日期之间的分钟间隔?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！