Query user availability in Postgres

Question

I'm building a website where users can post there availability (e.g. Mon & Tue 2pm-4pm) and book a chat with another user if available.

My database is Postgres (application in Rails) so I thought to let the database do the heavy lifting.

I want to display a calendar view of the users availability, so I tried to generate a list (time, available?) for a week to easily render the HTML.

Let's set up the schema and data:

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

And now I want to generate an availability list for a user, shown as a calendar with 30min blocks.

I came up with the following query: (for user id=1)

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;

The result looks somewhat like this but I can't get the availability right :(

        time         | available 
---------------------+-----------
 2019-06-26 00:00:00 | f
 2019-06-26 00:30:00 | f
 2019-06-26 01:00:00 | f
 2019-06-26 01:30:00 | f
...
 2019-06-26 23:30:00 | f
(48 rows)

Any help on improving this query or the general approach would be much appreciated. The users that view each others availability might have different time zones. The start and end times of booked chats is stored in UTC. Also would be great to have the timezone in the output.

Thanks for your help!

user_406551 · Answer

Alright it's been a while but I solved this by actually using a JS based calendar instead of writing my own in plain HTML.

I decided to use fullcalendar.io with the TimeGrid view - https://fullcalendar.io/docs/timegrid-view

You can specify a URL that fetches events from your server and properly renders them:

const calendarEl = document.getElementById('calendar-schedule')
var calendar = new FullCalendar.Calendar(calendarEl, {
  eventSources: [{
    url: '/availabilities',
    eventDataTransform: (event)  => {
      return {
        id: event.id,
        start: event.starts_at,
        end: event.ends_at,
        title: 'available'
      }
    }
  }]
})

This way your API just need to return the existing timeslots marked as available by the user.

user_10195691 · Answer

If i am not mistaken, putting

#/config/application.rb
    config.active_record.default_timezone = :utc

makes the time display with timezone aswell

Query user availability in Postgres

2 Answers