Find overlapping slots in SQL

1_README.md

This is an exploration of effective schema design for a "free time" appointment scheduler. It makes one strong assumption:

Your database is PostgreSQL 9.0+.

Given that, the result is not too bad - seems effective in this particular use-case. I don't really want to translate this into ActiveRecord though. *sigh*

Run this using:

$ psql -f 4_runner.sql

2_schema.sql

-- Schema queries

DROP DATABASE IF EXISTS date_frame_test;

CREATE DATABASE date_frame_test;

\c date_frame_test;

CREATE TABLE IF NOT EXISTS time_slots(
  id serial PRIMARY KEY,
  user_id integer,
  starts_at timestamp with time zone,
  ends_at timestamp with time zone
);

CREATE INDEX ON time_slots (user_id);
CREATE INDEX ON time_slots (starts_at);
CREATE INDEX ON time_slots (ends_at);

\d time_slots

INSERT INTO time_slots (id, user_id, starts_at, ends_at)
VALUES
  ( 1,  1, '2015-06-01 11:00-05', '2015-06-01 14:00-05'), -- [ ] - initial time slot to match
  ( 2,  2, '2015-06-01 12:00-05', '2015-06-01 15:00-05'), -- [x] - starts during, finishes after
  ( 3,  4, '2015-06-01 13:00-05', '2015-06-01 13:30-05'), -- [x] - starts during, finishes during
  ( 4,  5, '2015-06-01 10:30-05', '2015-06-01 11:30-05'), -- [x] - starts before, finishes during
  ( 5,  3, '2015-06-01 15:30-05', '2015-06-01 16:00-05'), -- [ ] - non-overlapping after
  ( 6,  3, '2015-06-01 08:30-05', '2015-06-01 09:00-05'), -- [ ] - non-overlapping before

  ( 7,  1, '2015-06-01 18:00-05', '2015-06-01 20:30-05'), -- [ ] - later slot to match for
  ( 8,  6, '2015-06-01 19:00-05', '2015-06-01 20:00-05'), -- [x] - later slot - starts during, finishes during

  ( 9,  7, '2015-06-01 11:00-05', '2015-06-01 11:30-05'), -- [x] - starts at the same time, finishes during
  (10,  7, '2015-06-01 13:00-05', '2015-06-01 14:00-05'), -- [x] - starts during, finishes at the same time

  (11,  8, '2015-06-01 10:30-05', '2015-06-01 11:00-05'), -- [ ] - starts before, finishes at start time
  (12,  8, '2015-06-01 14:00-05', '2015-06-01 14:30-05'), -- [ ] - starts at finish time, finishes after

  (13,  9, '2015-06-02 11:30-05', '2015-06-02 13:00-05'), -- [ ] - following day, times are during
  (14,  9, '2015-07-01 11:30-05', '2015-07-01 13:00-05'), -- [ ] - following month, times are during
  (15,  9, '2016-06-01 11:30-05', '2016-06-01 13:00-05'), -- [ ] - following year, times are during

  (16, 10, '2015-06-01 14:30-03', '2015-06-01 15:00-03'), -- [x] - different TZ, times during
  (17, 10, '2015-06-01 11:00-03', '2015-06-01 12:00-03')  -- [ ] - different TZ, times before, but look during
  ;

SELECT * FROM time_slots;

3_queries.sql

-- Data queries

\c date_frame_test;

-- User id to match
\echo
\echo '|> Find all slots overlapping all of user 1\'s timeslots without showing user 1\'s timeslots'
\echo '|> Expected ids are: 2, 3, 4, 8, 9, 10, 16'
\echo

SELECT *
FROM time_slots ts
INNER JOIN time_slots uts
  ON  ts.user_id != uts.user_id
  AND (
    (ts.starts_at >= uts.starts_at AND ts.ends_at <= uts.ends_at)
    OR (ts.starts_at BETWEEN uts.starts_at AND uts.ends_at AND ts.starts_at != uts.ends_at)
    OR (ts.ends_at   BETWEEN uts.starts_at AND uts.ends_at AND ts.ends_at != uts.starts_at)
  )
)
WHERE uts.user_id = 1
ORDER BY ts.id;
;

-- Using OVERLAPS

\echo
\echo '|> Same query, but using OVERLAPS to make it better'
\echo '|> Expected ids are: 2, 3, 4, 8, 9, 10, 16'
\echo

SELECT *
FROM time_slots ts
INNER JOIN time_slots uts
  ON  ts.user_id != uts.user_id
  AND (ts.starts_at, ts.ends_at) OVERLAPS (uts.starts_at, uts.ends_at)
WHERE uts.user_id = 1
ORDER BY ts.id
;

-- EXPLAIN the OVERLAPS query to show it winning like yeah

EXPLAIN SELECT *
FROM time_slots ts
INNER JOIN time_slots uts
  ON  (ts.starts_at, ts.ends_at) OVERLAPS (uts.starts_at, uts.ends_at)
  AND ts.user_id != uts.user_id
WHERE uts.user_id = 1
ORDER BY ts.id
;

4_runner.sql

\i 2_schema.sql
\i 3_queries.sql