如何找到两个时间戳记录之间每分钟的所有时间戳值间隔

本文介绍了如何找到两个时间戳记录之间每分钟的所有时间戳值间隔的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个具有三个字段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

返回