CODEWITHSUNDEEP
sqlpostgresql
jamesharker
jamesharker

Reputation: 729

Calculate Resource Availability by Time Period in Postgres

Suppose I have a reservations table that stores information regarding when a particular resource has been reserved.

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

And it contains the data:

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"

If I were to generate a series of sequential time stamps with 15 minute intervals like so:

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;

which would give me:

 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"

How do I find the time intervals where there are no reservations? So the data I'm looking for is:

 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 - I don't have to use generate_series but I thought it looked like it could be useful! My only requirement is that I find a list of time intervals when the resource is not booked.

Upvotes: 0

Views: 597

Answers (2)

joop
joop

Reputation: 4523

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

Upvotes: 1

khampson
khampson

Reputation: 15356

I would recommend generate_series in conjunction with the OVERLAPS operator and some creative use of temp tables.

I believe this should do the trick in general (would need tweaks to the timeslots generation for larger time ranges, and may need some perf. tuning for larger data sets):

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;

This yields the following results (11-11:15 and 11:15-11:30 as 2 separate intervals instead of 1, which is what the 15 minute blocks would yield):

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

Upvotes: 2

Related Questions