Ts计算两个范围之间的差异

Question

我有两个表free_time和appointment。两者都包含 tsrange。

如何编写一个查询(或函数)来确定从freetime中"减去"appointment差后的实际空闲时间？

INSERT INTO free_time(freetime)
VALUES('[2017-04-19 09:00, 2017-04-19 12:30)');
INSERT INTO appointment(appointment)
VALUES('[2017-04-19 10:30, 2017-04-19 11:30)');

我希望结果是这样的：

["2017-04-19 9:00","2017-04-19 10:30:00"), 
["2017-04-19 11:30:00","2017-04-19 12:30:00")

Answer 1

您必须从文档中分离

范围

如果生成的范围需要包含两个不相交的子范围，则联合和差分运算符将失败，因为无法表示此类范围。

为此，您可以使用lower和upper

SELECT tsrange(  lower(freetime), lower(appointment)  )  AS before_appointment,
tsrange(  upper(appointment), upper(freetime)  )  AS after_appointment
FROM ( VALUES
(
'[2017-04-19 09:00, 2017-04-19 12:30)'::tsrange,
'[2017-04-19 10:30, 2017-04-19 11:30)'::tsrange
)
) AS t(freetime,appointment)
WHERE freetime @> appointment;
before_appointment               |               after_appointment               
-----------------------------------------------+-----------------------------------------------
["2017-04-19 09:00:00","2017-04-19 10:30:00") | ["2017-04-19 11:30:00","2017-04-19 12:30:00")
(1 row)