PG user availability

gistfile1.txt

$ createdb demo
$ psql demo
demo=# CREATE TABLE users (id SERIAL PRIMARY KEY, timezone VARCHAR);
demo=# CREATE TABLE timeslots (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, days INTEGER[] NOT NULL, start_time TIME NOT NULL, end_time TIME NOT NULL);
demo=# CREATE TABLE chats (id SERIAL PRIMARY KEY, host_user_id INTEGER NOT NULL, guest_user_id INTEGER NOT NULL, start_time TIMESTAMP NOT NULL, end_time TIMESTAMP NOT NULL);
demo=# INSERT INTO users (timezone) VALUES ('America/Los_Angeles');
demo=# INSERT INTO users (timezone) VALUES ('America/New_York');
demo=# INSERT INTO timeslots (user_id, days, start_time, end_time) VALUES (1, '{3}', '10:00', '15:00');
demo=# INSERT INTO chats (host_user_id, guest_user_id, start_time, end_time) VALUES (1, 2, '2019-06-26 11:30:00', '2019-06-26 12:30:00');
demo=# WITH time_range AS (
  SELECT generate_series(
    '2019-06-26 00:00:00'::timestamp,
    '2019-06-26 23:59:59'::timestamp,
    '30 minutes'
  ) AS time
)
SELECT time, EXISTS (
  SELECT 1
  FROM timeslots
  WHERE user_id = 1
    AND extract(dow from time) = ANY(days)
    AND start_time <= time::time
    AND (time + '30 minutes'::interval)::time <= end_time
) AND NOT EXISTS (
  SELECT 1
  FROM chats
  WHERE (
    host_user_id = 1
    OR guest_user_id = 1
  ) AND start_time <= time
  AND (time + '30 minutes'::interval) <= end_time
) AS available
FROM time_range;