sql - 在 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"

注意 - 我不必使用 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 分钟 block 会产生的结果):

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