在PostgreSQL中如何查找预订表中的第一个空闲时间

Question

预订表包含预订的开始日期、开始时间和持续时间。开始时间以工作日的半小时为增量，在工作时间8:00到18:00之间进行。持续时间也是以每半小时为增量计算的。

CREATE TABLE reservation (
  startdate date not null,  -- start date
  starthour numeric(4,1) not null , -- start hour 8 8.5 9 9.5  ..  16.5 17 17.5
  duration  Numeric(3,1) not null, -- duration by hours 0.5 1 1.5 .. 9 9.5 10
  primary key (startdate, starthour)
);

如果需要，表结构可以更改。

如何找到第一个空闲半小时的桌子，这个桌子没有被预定？ 例如，如果表格包含：

startdate   starthour  duration 
14          9           1              -- ends at 9:59
14          10          1.5            -- ends at 11:29, e.q there is 30 minute gap before next
14          12          2
14          16          2.5

结果应该是：

starthour  duration
11.5       0.5

可能应该使用PostgreSql 9.2的窗口函数来查找第一行，其starthour大于上一行starthour + duration
如何编写选择语句以返回此信息？

- Andrus

---

在这里也同时发布到了PostgreSQL邮件列表：http://archives.postgresql.org/message-id/C4548EC84E8746E9B4BAA92310F1A4FE@dell2。因为作者在Pg列表帖子中链接到了这个问题，所以没有进行踩票处理。 - Craig Ringer

Answer 1

Postgres 9.2有范围类型，我建议使用它们。

create table reservation (reservation tsrange);
insert into reservation values 
('[2012-11-14 09:00:00,2012-11-14 10:00:00)'), 
('[2012-11-14 10:00:00,2012-11-14 11:30:00)'), 
('[2012-11-14 12:00:00,2012-11-14 14:00:00)'), 
('[2012-11-14 16:00:00,2012-11-14 18:30:00)');

ALTER TABLE reservation ADD EXCLUDE USING gist (reservation WITH &&);

"

“EXCLUDE USING gist”创建了一个索引，不允许插入重叠的条目。您可以使用以下查询来查找间隙（vyegorov查询的变体）：

"

with gaps as (
  select 
    upper(reservation) as start, 
    lead(lower(reservation),1,upper(reservation)) over (ORDER BY reservation) - upper(reservation) as gap 
  from (
    select * 
    from reservation 
    union all values 
      ('[2012-11-14 00:00:00, 2012-11-14 08:00:00)'::tsrange), 
      ('[2012-11-14 18:00:00, 2012-11-15 00:00:00)'::tsrange)
  ) as x
) 
select * from gaps where gap > '0'::interval;

'union all values'是一种掩盖非工作时间的方法，因此您只能在上午8点到下午6点之间进行预订。

以下是结果：

        start        |   gap    
---------------------+----------
 2012-11-14 08:00:00 | 01:00:00
 2012-11-14 11:30:00 | 00:30:00
 2012-11-14 14:00:00 | 02:00:00

文档链接： - 范围类型 - 范围类型介绍（PDF）

- mys

---

非常感谢。这只能找到单日的空缺。如何在工作时间8:00至18:00内找到任何工作日（不包括假期表中的周六、周日和公共假期）的空缺？ - Andrus

我在https://dev59.com/zmrXa4cB1Zd3GeqPEvuz上发布了这个单独的问题，如何在PostgreSQL中排除周末和公共假期。 - Andrus

我正在尝试为股票订单创建相同的“间隙”，也许你可以看一下？http://stackoverflow.com/questions/34053936/getting-unavailable-dates-for-renting-a-product-that-has-stocks - CularBytes

Answer 2

也许不是最好的查询，但它可以实现你想要的功能：

WITH
times AS (
    SELECT startdate sdate,
        startdate + (floor(starthour)||'h '||
           ((starthour-floor(starthour))*60)||'min')::interval shour,
        startdate + (floor(starthour)||'h '||
           ((starthour-floor(starthour))*60)||'min')::interval 
           + (floor(duration)||'h '||
             ((duration-floor(duration))*60)||'min')::interval ehour
      FROM reservation),
gaps AS (
    SELECT sdate,shour,ehour,lead(shour,1,ehour)
       OVER (PARTITION BY sdate ORDER BY shour) - ehour as gap
      FROM times)
SELECT * FROM gaps WHERE gap > '0'::interval;

一些注意事项：

1. 最好不要将事件的时间和日期分开。如果必须分开，那么请使用标准类型；
2. 如果无法使用标准类型，请创建一个函数将“数字”小时转换为“时间”格式。

- vyegorov

---

同意 - 如果您必须将时间与日期分开存储，请使用“TIME”。不要使用“NUMERIC”；正如您可以从上面的查询中看到的那样，它会使处理时间和间隔变得复杂。 - Craig Ringer