如何找到两个时间戳记录之间每分钟的所有时间戳值间隔 [英] How to find all the timestamp values interval by each minute between the two timestamp records

问题描述

我有一个具有三个字段ID(整数)的表-唯一，打开日期(日期时间)，关闭日期(日期时间):

I have a table having three fields Id (Integer) - Unique, Open Date (Datetime), Close Date(DateTime):

Id  Open Date                  Close Date
1   2019-07-03 16:28:39.497    2019-07-04 16:28:39.497
2   2019-07-04 15:28:39.497    2019-07-05 19:28:39.497
…..N        

我想计算打开日期和关闭日期之间的所有时间戳，以每分钟为间隔.

I want to calculate the all the timestamps between open date and close date with an interval of each minute.

所以我想要的最终输出是这样的:

So the final output I want is like this:

Id  Open Date             Close Date                   TimeStamp Range
1   2019-07-03 16:28:39.497   2019-07-04 16:28:39.497   2019-07-03 16:29:00.0000
1   2019-07-03 16:28:39.497   2019-07-04 16:28:39.497   2019-07-03 16:30:00.0000
1   2019-07-03 16:28:39.497   2019-07-04 16:28:39.497   2019-07-03 16:31:00.0000
1   2019-07-03 16:28:39.497   2019-07-04 16:28:39.497   …..........................
1   2019-07-03 16:28:39.497   2019-07-04 16:28:39.497   2019-07-04 16:27:00.0000
2   2019-07-04 15:28:39.497   2019-07-05 19:28:39.497   2019-07-04 15:29:00.0000
2   2019-07-04 15:28:39.497   2019-07-05 19:28:39.497   2019-07-04 15:30:00.0000
2   2019-07-04 15:28:39.497   2019-07-05 19:28:39.497   2019-07-04 15:31:00.0000
2   2019-07-04 15:28:39.497   2019-07-05 19:28:39.497   ….................................
2   2019-07-04 15:28:39.497   2019-07-05 19:28:39.497   2019-07-05 19:27:00.0000
N   …............................   …...........................    …......................................

有人会为此编写SQL查询吗?

Would someone write the SQL query for this?

推荐答案

以下是使用临时计数/数字表和Cross Apply

Here is one option using an ad-hoc tally/numbers table and a Cross Apply

示例

Declare @YourTable Table ([Id] int,[Open Date] datetime,[Close Date] datetime)  Insert Into @YourTable Values 
 (1,'2019-07-03 16:28:39.497','2019-07-04 16:28:39.497')
,(2,'2019-07-04 15:28:39.497','2019-07-05 19:28:39.497')

Select A.*
      ,TSRange = DateAdd(Minute,N,convert(varchar(16),[Open Date],20))
  From @YourTable A
  Cross Apply (
                Select Top (DateDiff(MINUTE,[Open Date],[Close Date])-1) N=Row_Number() Over (Order By (Select NULL)) 
                  From master..spt_values n1, master..spt_values n2
              )  B

返回

这篇关于如何找到两个时间戳记录之间每分钟的所有时间戳值间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！