postgresql - 两张表之间的交集间隔日期

您好，我需要将两个表与间隔日期相交

时间轴1

start               | end
---------------------------------------------
2016-12-19 08:00:00 | 2016-12-19 08:30:00
2016-12-19 09:30:00 | 2016-12-19 11:00:00

时间轴2

start               | end
---------------------------------------------
2016-12-19 08:30:00 | 2016-12-19 10:00:00
2016-12-19 10:30:00 | 2016-12-19 11:00:00

这是我尝试的SQL:

SELECT * FROM start, end FROM timeline1
INTERSECT
SELECT * FROM start, end FROM timeline2

需要这样的结果:

总计:00:60:00

最佳答案

使用range operators * and &&:

select tsrange(t1."start", t1."end") * tsrange(t2."start", t2."end") as "intersections"
from timeline1 t1
join timeline2 t2
on tsrange(t1."start", t1."end") && tsrange(t2."start", t2."end");

                 intersections
-----------------------------------------------
 ["2016-12-19 09:30:00","2016-12-19 10:00:00")
 ["2016-12-19 10:30:00","2016-12-19 11:00:00")
(2 rows)

间隔总和:

select sum(upper("intersections")- lower("intersections")) as total
from (
    select tsrange(t1."start", t1."end") * tsrange(t2."start", t2."end") as "intersections"
    from timeline1 t1
    join timeline2 t2
    on tsrange(t1."start", t1."end") && tsrange(t2."start", t2."end")
) s

  total
----------
 01:00:00
(1 row)

关于postgresql - 两张表之间的交集间隔日期，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/41220843/