Tsrange - 计算两个范围之间的差异 [英] Tsrange - Calculating the difference between two ranges

问题描述

我有两个表 free_time 和 appointment.两者都包含 tsranges.

I have two tables free_time and appointment. Both contain tsranges.

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

How do I write a query (or function) that determines the actual free time after "subtracting" the difference the appointment from the freetime?

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")

推荐答案

你必须打破范围，从 文档

如果结果范围需要包含两个不相交的子范围，则联合和差异运算符将失败，因为无法表示这样的范围.

The union and difference operators will fail if the resulting range would need to contain two disjoint sub-ranges, as such a range cannot be represented.

为了做到这一点，您可以使用 lower 和 upper

In order to do this you can use lower, and 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)

这篇关于Tsrange - 计算两个范围之间的差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！