sql - 在 postgresql 中获取空/打开时间范围

Question

我正在尝试找到开放类次，其中:

第一类从早上 6 点开始

最后一类于上午 12 点结束

IE:

鉴于以下数据/天:

start_time | end_time
-----------|---------
9  AM      |  3  PM
5  PM      |  10 PM

预期成绩:

start_time | end_time
-----------|---------
6  AM      | 9  AM
3  PM      | 5  PM
10 PM      | 12 AM

这是我尝试过的，但它不起作用(我离正确答案还很远)

SELECT *
FROM WORKERS_SCHEDULE
WHERE START_TIME not BETWEEN 
    ANY (SELECT START_TIME FROM WORKERS_SCHEDULE)
    AND (SELECT START_TIME FROM WORKERS_SCHEDULE)

start_time和 end_time是数据类型 TIME .

Answer 1

这是使用 union all 执行此操作的一种方法和窗口函数:

select *
from (

    select '06:00:00'::time start_time, min(start_time) end_time from mytable
    union all 
    select end_time, lead(start_time) over(order by start_time) from mytable
    union all 
    select max(end_time), '23:59:59'::time from mytable
) t
where start_time <> end_time

彻底解释它的工作原理有点复杂，但是:第一个联合查询计算早上 6 点到第一次类次开始之间的时间间隔，第二个子查询处理声明的类次，最后一个处理最后一次类次和午夜之间的时间间隔.然后，外部查询过滤有间隙的记录。要了解它是如何工作的，您可以独立运行子查询，并查看开始和结束是如何调整的。

Demo on DB Fiddle :

开始时间 |时间结束
:--------- | :-------
06:00:00 | 09:00:00
15:00:00 | 17:00:00
22:00:00 | 23:59:59