Fetch potential valid time slots for a "workshops" table where multiple workshops may occur simultaneously, with non-concurrent start/end times.

get_time_slots.sql

WITH
  times AS (
    SELECT ROW_NUMBER() OVER (ORDER BY time) AS sort, time
      FROM (
        SELECT DISTINCT time
          FROM (
            SELECT start_time AS time
              FROM workshops
              WHERE TO_CHAR(day, 'FMDay') = 'Friday'
            UNION
            SELECT finish_time AS time
              FROM workshops
              WHERE TO_CHAR(day, 'FMDay') = 'Friday'
          ) AS all_times
      ) AS distinct_times
  ),

  times_plus_one AS (
    SELECT sort,
           time,
           (
             SELECT time
              FROM times t
              WHERE (times.sort + 1) = t.sort
           ) AS time_plus_one
      FROM times
  ),

  all_times AS (
    SELECT *
      FROM times_plus_one
      WHERE time_plus_one IS NOT NULL
      ORDER BY sort
  )

  SELECT *
    FROM all_times
    WHERE EXISTS (
            SELECT 1
              FROM workshops
              WHERE TO_CHAR(day, 'FMDay') = 'Friday' AND
                    (start_time, finish_time) OVERLAPS (time, time_plus_one)
          );