如何在PostgreSQL的预订表中找到第一次空闲时间 [英] How to find first free time in reservations table in PostgreSql

问题描述

预订表包含预订的开始日期，开始时间和持续时间。
开始时间是工作时间8:00 .. 18:00（工作日）以半小时为增量。
持续时间也是一天中的半小时增量。

Reservation table contains reservations start dates, start hours and durations. Start hour is by half hour increments in working hours 8:00 .. 18:00 in work days. Duration is also by half hour increments in day.

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

表结构可以根据需要更改。

table structure can changed if required.

如何在未保留的表格中查找第一个免费的半小时？
表格是否包含

How to find first free half hour in table which is not reserved ? E.q if table contains

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窗口函数查找起始时间大于
的第一行上一行开始时间+持续时间

如何编写返回此信息的选择语句？

Probably PostgreSql 9.2 window function should used to find first row whose starthour is greater than previous row starthour + duration
How to write select statement which returns this information ?

推荐答案

Postgres 9.2具有

Postgres 9.2 has range type and I would recommend to use them.

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 &&);

使用要点排除创建索引，该索引不允许插入重叠的条目。您可以使用以下查询找到空白（Vyegorov查询的变体）：

"EXCLUDE USING gist" creates index which disallows to inset overlapping entries. You can use the following query to find gaps (variant of vyegorov's query):

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;

统一所有值掩盖了非工作时间，因此您只能在上午8点至晚上18点之间进行预订。

'union all values' masks out non working times hence you can make reservation between 8am and 18pm only.

结果如下：

        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

文档链接：
-http://www.postgresql.org/docs/9.2/static/rangetypes.html 范围类型
- https://wiki.postgresql.org/images/7/73/Range- types-pgopen-2012.pdf

Documentation links: - http://www.postgresql.org/docs/9.2/static/rangetypes.html "Range Types" - https://wiki.postgresql.org/images/7/73/Range-types-pgopen-2012.pdf

这篇关于如何在PostgreSQL的预订表中找到第一次空闲时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！