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:
我有一个startTime和endTimes表。我需要在几分钟内生成这两个日期之间的间隔表。这是一些示例数据:
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')
What I'd like to see is for each myId
a set of dates of interval @intervalMinutes
.
我想看到的是每个myId的一组日期间隔@intervalMinutes。
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.
因此,如果我们将@intervalMinutes设置为10,那么我将在第一行中看到2016-07-10 08:00和2016-07-10 09:00之间的6个日期的列表,以10分钟为增量。
3 个解决方案
#1
1
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;
#2
2
A numbers/tally table would do the trick as Gordon mentioned. However, I use a UDF to create dynamic date ranges.
正如戈登所提到的那样,数字/理货表可以解决问题。但是,我使用UDF来创建动态日期范围。
For example
例如
Select * from [dbo].[udf-Create-Range-Date]('2016-07-10 08:00','2016-07-10 09:00','MI',10)
Returns
返回
RetVal
2016-07-10 08:00:00.000
2016-07-10 08:10:00.000
2016-07-10 08:20:00.000
2016-07-10 08:30:00.000
2016-07-10 08:40:00.000
2016-07-10 08:50:00.000
2016-07-10 09:00:00.000
The UDF
UDF
CREATE FUNCTION [dbo].[udf-Create-Range-Date] (@DateFrom datetime,@DateTo datetime,@DatePart varchar(10),@Incr int)
Returns
@ReturnVal Table (RetVal datetime)
As
Begin
With DateTable As (
Select DateFrom = @DateFrom
Union All
Select Case @DatePart
When 'YY' then DateAdd(YY, @Incr, df.dateFrom)
When 'QQ' then DateAdd(QQ, @Incr, df.dateFrom)
When 'MM' then DateAdd(MM, @Incr, df.dateFrom)
When 'WK' then DateAdd(WK, @Incr, df.dateFrom)
When 'DD' then DateAdd(DD, @Incr, df.dateFrom)
When 'HH' then DateAdd(HH, @Incr, df.dateFrom)
When 'MI' then DateAdd(MI, @Incr, df.dateFrom)
When 'SS' then DateAdd(SS, @Incr, df.dateFrom)
End
From DateTable DF
Where DF.DateFrom < @DateTo
)
Insert into @ReturnVal(RetVal) Select DateFrom From DateTable option (maxrecursion 32767)
Return
End
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','YY',1)
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','DD',1)
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-31','MI',15)
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-02','SS',1)
#3
1
You can use recursive query like this :
您可以使用这样的递归查询:
declare @intervalMinutes int = 10
declare @myDates table (
myId int primary key identity,
startTime datetime,
endTime datetime
)
DECLARE @startTime DATETIME = '2016-07-10 08:00'
DECLARE @endTime DATETIME = '2016-07-10 09:00'
;WITH CTE AS
(
SELECT @startTime st
UNION ALL
SELECT dateadd(MINUTE,@intervalMinutes,st) st
FROM cte
where dateadd(MINUTE,@intervalMinutes,st) < @endTime
)
INSERT INTO @myDates(startTime,endTime)
SELECT st,dateadd(MINUTE,@intervalMinutes,st) FROM cte
SELECT * FROm @myDates
#1
1
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;
#2
2
A numbers/tally table would do the trick as Gordon mentioned. However, I use a UDF to create dynamic date ranges.
正如戈登所提到的那样,数字/理货表可以解决问题。但是,我使用UDF来创建动态日期范围。
For example
例如
Select * from [dbo].[udf-Create-Range-Date]('2016-07-10 08:00','2016-07-10 09:00','MI',10)
Returns
返回
RetVal
2016-07-10 08:00:00.000
2016-07-10 08:10:00.000
2016-07-10 08:20:00.000
2016-07-10 08:30:00.000
2016-07-10 08:40:00.000
2016-07-10 08:50:00.000
2016-07-10 09:00:00.000
The UDF
UDF
CREATE FUNCTION [dbo].[udf-Create-Range-Date] (@DateFrom datetime,@DateTo datetime,@DatePart varchar(10),@Incr int)
Returns
@ReturnVal Table (RetVal datetime)
As
Begin
With DateTable As (
Select DateFrom = @DateFrom
Union All
Select Case @DatePart
When 'YY' then DateAdd(YY, @Incr, df.dateFrom)
When 'QQ' then DateAdd(QQ, @Incr, df.dateFrom)
When 'MM' then DateAdd(MM, @Incr, df.dateFrom)
When 'WK' then DateAdd(WK, @Incr, df.dateFrom)
When 'DD' then DateAdd(DD, @Incr, df.dateFrom)
When 'HH' then DateAdd(HH, @Incr, df.dateFrom)
When 'MI' then DateAdd(MI, @Incr, df.dateFrom)
When 'SS' then DateAdd(SS, @Incr, df.dateFrom)
End
From DateTable DF
Where DF.DateFrom < @DateTo
)
Insert into @ReturnVal(RetVal) Select DateFrom From DateTable option (maxrecursion 32767)
Return
End
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','YY',1)
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2020-10-01','DD',1)
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-31','MI',15)
-- Syntax Select * from [dbo].[udf-Create-Range-Date]('2016-10-01','2016-10-02','SS',1)
#3
1
You can use recursive query like this :
您可以使用这样的递归查询:
declare @intervalMinutes int = 10
declare @myDates table (
myId int primary key identity,
startTime datetime,
endTime datetime
)
DECLARE @startTime DATETIME = '2016-07-10 08:00'
DECLARE @endTime DATETIME = '2016-07-10 09:00'
;WITH CTE AS
(
SELECT @startTime st
UNION ALL
SELECT dateadd(MINUTE,@intervalMinutes,st) st
FROM cte
where dateadd(MINUTE,@intervalMinutes,st) < @endTime
)
INSERT INTO @myDates(startTime,endTime)
SELECT st,dateadd(MINUTE,@intervalMinutes,st) FROM cte
SELECT * FROm @myDates