两个时间戳与分类为1分钟桶之间的SQL差异

Question

我正在开发一个示例项目，在该项目中，我使用了一个名为TDV()的工具，该工具支持oracle和Postgres。我有两个带有一些值的时间戳列startDate和endDate，我试图将数据扩展到1分钟，但是我很难以所需的格式获得它。开始日期和结束日期的差别不是常数，有时是3分钟、5分钟或7分钟。我做了一些研究，但找不到正确的解决办法。

样本数据

+--------------------+--------------------+--------+
|     StartDate      |      EndDate       | Val1   |
+--------------------+--------------------+--------+
| 3/22/2020 10:06:30 | 3/22/2020 10:11:29 |     10 |
| 3/22/2020 14:20:51 | 3/22/2020 14:25:50 |     22 |
+--------------------+--------------------+--------+

期望结果

+--------------------+--------------------+--------------------+--------+
|     StartDate      |      EndDate       |     Time_1Min      | Val1   |
+--------------------+--------------------+--------------------+--------+
| 3/22/2020 10:06:30 | 3/22/2020 10:11:29 | 3/22/2020 10:07:00 |     10 |
| 3/22/2020 10:06:30 | 3/22/2020 10:11:29 | 3/22/2020 10:08:00 |     10 |
| 3/22/2020 10:06:30 | 3/22/2020 10:11:29 | 3/22/2020 10:09:00 |     10 |
| 3/22/2020 10:06:30 | 3/22/2020 10:11:29 | 3/22/2020 10:10:00 |     10 |
| 3/22/2020 10:06:30 | 3/22/2020 10:11:29 | 3/22/2020 10:11:00 |     10 |
| 3/22/2020 14:20:51 | 3/22/2020 14:25:50 | 3/22/2020 14:21:00 |     22 |
| 3/22/2020 14:20:51 | 3/22/2020 14:25:50 | 3/22/2020 14:22:00 |     22 |
| 3/22/2020 14:20:51 | 3/22/2020 14:25:50 | 3/22/2020 14:23:00 |     22 |
| 3/22/2020 14:20:51 | 3/22/2020 14:25:50 | 3/22/2020 14:24:00 |     22 |
| 3/22/2020 14:20:51 | 3/22/2020 14:25:50 | 3/22/2020 14:25:00 |     22 |
+--------------------+--------------------+--------------------+--------+

Answer 1

您可以使用递归CTE：

with recursive cte as (startdate, enddate, time_1min, value) as
      select startdate, enddate, trunc(startdate) + interval '1 minute', value
      from t
      union all
      select startdate, enddate, time_1min + interval '1 minute', value
      from cte
      where time_1min < enddate
     )
select *
from cte;

Answer 2

您说您正在使用一个同时支持Oracle和Postgres的工具，但是您可以使用net让我们知道该工具是什么。通过我们可以建议的工具，没有多少东西。因此，我提供了一个基于解决方案的Postgres 解决方案，而不是递归的generate_series。

select startdate, enddate, time_1min, value 
  from t 
 cross join generate_series(date_trunc('minute',startdate)+interval '1 minute)'
                           ,date_trunc('minute',enddate)
                           , interval '1 minute'
                           ) gs(time_1min)
 order by startdate, time_1min;