时间戳

首页 > 解决方案 > 在 postgresql 中获取空/打开时间范围

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

问题描述

我正在尝试在以下位置找到空缺的班次：

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。

标签： sqlpostgresqldatedatetimeunion

解决方案

这是使用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 点和第一个班次开始之间的间隔，第二个子查询处理声明的班次，最后一个处理最后一个班次和午夜之间的间隔. 然后，外部查询过滤有间隙的记录。要了解它是如何工作的，您可以独立运行子查询，并查看开始和结束是如何调整的。

DB Fiddle 上的演示：

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

推荐阅读