postgre如何在SQL中将时间段列表转换为日期列表

Question

我有一个活动事件的时间段表，格式为：

Incident_Start	Incident_End
2022年1月1日01:05
2022年1月2日05:00
2022年5月2日13:00

您可以尝试以下解决方案A/生成一组与日历日期相对应的时间戳范围B/选择与事件时间范围相交的日期C/计算日期/事件时间交叉点的持续时间(以分钟为单位(：

SELECT lower(r.date_interval) AS Date
, floor(EXTRACT(EPOCH FROM least(upper(r.date_interval), Incident_End) - greatest(lower(r.date_interval), Incident_Start))/60) AS Incident_Minutes
FROM your_table AS t
INNER JOIN 
( SELECT tsrange(t, t + interval '1 day') AS date_interval
FROM generate_series('1/1/2022' :: timestamp, '1/12/2022' :: timestamp, '1 day' :: interval) AS t
) AS r
ON r.date_interval && tsrange(Incident_Start, Incident_End)

查看dbfiddle 中的结果

您可以cross join事件开始和结束之间的日期时间序列：

select d::date, extract(epoch from ((case when i.incident_end::date = d::date then i.incident_end else d::date + interval '1 day' end) - (case when d::date = i.incident_start::date then i.incident_start else d::date end)))/60 from incidents i 
cross join generate_series(i.incident_start, i.incident_end, interval '1 day') d

参见小提琴