我有一个包含活动事件的时间段表,其格式为:
| 事件_开始|事件_结束|
| - -|- -|
| 2022年1月1日01:05| 2022年1月1日03:00|
| 2022年1月2日05:00| 2022年1月5日12时34分|
| 2022年2月5日13时|2022年2月6日16时22分|
| 一个人。|一个人。|
现在,我需要将其转换为一个日期表,其中包含事件在每个日期期间处于活动状态的总分钟数,如下所示:
| 日期|事件分钟数|
| - -|- -|
| 2022年1月1日|一百一十五个|
| 2022年1月2日|小行星1140|
| 2022年1月3日|小行星1440|
| 2022年1月4日|小行星1440|
| 2022年1月5日|七五四|
| 2022年1月6日|第0页|
| 一个人。|一个人。|
我可以通过Python/JavaScript轻松地做到这一点,比如(在伪代码中,非常天真):
dates = [dates between start_date, end_date]
for (date in dates):
if (impact_periods.filter(start_date <= date && end_date >= date).length > 0):
outage_mins = 1440
else if (impact_periods.filter(start_date >= date && end_date <= date).length > 0):
outage_mins = sum(impact_periods.filter(start_date >= date && end_date <= date).outage_mins)
etc
现在,我想使用SQL查询来完成此操作,但我不确定如何完成。显然,我将从创建一个日期表开始,该表位于我感兴趣的日期之间:
SELECT
dd day_start,
dd::date + 1 - interval '1 sec' AS day_end
FROM
generate_series (
'date_start' :: timestamp,
'date_end' :: timestamp,
'1 day' :: interval
) dd
但现在我不知道如何计算每天的影响分钟数,因为有些事件可能在一天之前开始,在一天中结束,或者在一天中开始,在一天之后结束。
有没有人能给我指出如何解决这个问题的正确方向?任何帮助都非常感谢!
2条答案
按热度按时间gudnpqoy1#
您可以尝试以下解决方案:A/生成一组与日历日相对应的时间戳范围B/选择与事件时间范围相交的日期C/计算日期/事件时间交叉点的持续时间(以分钟为单位):
查看dbfiddle中的结果
6tdlim6h2#
您可以
cross join
事件开始和结束之间的日期的日期时间序列:See fiddle