按Postgres中的时间段计算资源可用性

Question

假设我有一个reservations表，它存储有关何时保留特定资源的信息。

CREATE TABLE reservations (
  start_time timestamp not null,
  finish_time timestamp not null,
  id serial primary key
);

它包含以下数据：

id    start_time               finish_time
1    "2014-05-01 09:30:00"    "2014-05-01 10:00:00"
2    "2014-05-01 10:15:00"    "2014-05-01 11:00:00"
3    "2014-05-01 11:30:00"    "2014-05-01 11:45:00"

如果我生成一系列间隔15分钟的连续时间戳，如下所示：

SELECT timestamp as start_time, (timestamp + '15 minutes') AS finish_time FROM generate_series('2014-05-01 09:00'::timestamp, '2014-05-01 11:45'::timestamp, interval '15 minutes') AS timestamp;

这会给我：

 start_time             finish_time
"2014-05-01 09:00:00"  "2014-05-01 09:15:00"
"2014-05-01 09:15:00"  "2014-05-01 09:30:00"
"2014-05-01 09:30:00"  "2014-05-01 09:45:00"
...
"2014-05-01 11:15:00"  "2014-05-01 11:30:00"
"2014-05-01 11:30:00"  "2014-05-01 11:45:00"
"2014-05-01 11:45:00"  "2014-05-01 12:00:00"

如何找到没有预订的时间间隔？所以我要找的数据是：

 start_time             finish_time
"2014-05-01 09:00:00"  "2014-05-01 09:15:00"
"2014-05-01 09:15:00"  "2014-05-01 09:30:00"
"2014-05-01 10:00:00"  "2014-05-01 10:15:00"
"2014-05-01 11:00:00"  "2014-05-01 11:15:00"
"2014-05-01 11:15:00"  "2014-05-01 11:30:00"
"2014-05-01 11:45:00"  "2014-05-01 12:00:00"

N.B-我不必使用generate_series，但我觉得它看起来很有用！我唯一的要求是，当资源未被预订时，我要找到一个时间间隔列表。

Answer 1

我建议generate_series与OVERLAPS运算符结合使用，并创造性地使用临时表。

我认为这应该在一般情况下起作用(需要对较大时间范围的时隙生成进行调整，并且可能需要对较大数据集进行一些性能调整(：

INSERT INTO reservations (start_time, finish_time)
VALUES ('2014-05-01 09:30:00',  '2014-05-01 10:00:00'),
       ('2014-05-01 10:15:00',  '2014-05-01 11:00:00'),
       ('2014-05-01 11:30:00',  '2014-05-01 11:45:00');
CREATE TABLE timeslots AS
SELECT timestamp as start_time, (timestamp + '15 minutes') AS finish_time
FROM generate_series('2014-05-01 09:00'::timestamp,
                     '2014-05-01 11:45'::timestamp,
                     interval '15 minutes') AS timestamp;

CREATE TEMP TABLE slots_in_use AS
SELECT DISTINCT t.start_time, t.finish_time
FROM timeslots AS t
JOIN reservations AS r ON ( ((t.start_time, t.finish_time) OVERLAPS (r.start_time, r.finish_time)))
ORDER BY t.start_time, t.finish_time;
CREATE TEMP TABLE free_timeslots AS
SELECT *
FROM timeslots;
DELETE FROM free_timeslots as x
WHERE EXISTS (SELECT 1
              FROM slots_in_use AS s
              WHERE x.start_time = s.start_time
                AND x.finish_time = s.finish_time
             );
SELECT *
FROM free_timeslots
ORDER BY start_time, finish_time;

这产生了以下结果(11-11:15和11:15-11:30作为2个单独的间隔，而不是1，这是15分钟块将产生的结果(：

START_TIME  FINISH_TIME
May, 01 2014 09:00:00+0000  May, 01 2014 09:15:00+0000
May, 01 2014 09:15:00+0000  May, 01 2014 09:30:00+0000
May, 01 2014 10:00:00+0000  May, 01 2014 10:15:00+0000
May, 01 2014 11:00:00+0000  May, 01 2014 11:15:00+0000
May, 01 2014 11:15:00+0000  May, 01 2014 11:30:00+0000
May, 01 2014 11:45:00+0000  May, 01 2014 12:00:00+0000

Answer 2

SELECT slots.slot_start, slots.slot_end
FROM (
        SELECT tick as slot_start, tick + '15 minutes'::interval AS slot_end
        FROM generate_series('2014-05-01 09:00'::timestamp
                           , '2014-05-01 11:45'::timestamp, interval '15 minutes') AS tick
        ) slots
WHERE NOT EXISTS (
        SELECT * FROM reservations rx
        WHERE (rx.start_time, rx.finish_time) OVERLAPS (slots.slot_start, slots.slot_end)
        )
        ;