PostgreSQL timeslots, rrulesets and booking timetables

gistfile1.txt

-- First create schema/data data for tests

DROP  TABLE api.slot CASCADE;

CREATE TABLE api.slot (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name        TEXT,
  latitude    NUMERIC,
  longitude   NUMERIC,
  geog        geography(Point, 4326) DEFAULT NULL
);

CREATE INDEX slot_geog_points_gix ON api.slot USING GIST ( geog );

GRANT SELECT ON api.slot TO app_anonymous;


INSERT INTO api.slot (id, name, latitude, longitude, geog)
VALUES ('8a45abd7-8c79-40f9-94b8-899cb3ff38fe', 'Beach', '57.159754', '-2.079346', 'SRID=4326;POINT(-2.079346 57.159754)');

INSERT INTO api.slot (id, name, latitude, longitude, geog)
VALUES ('7c209986-a9ae-4d33-b11d-fb72954ca00c', 'Park Street', '57.152317', '-2.090250', 'SRID=4326;POINT(-2.090250 57.152317)');

INSERT INTO api.slot (id, name, latitude, longitude, geog)
VALUES ('108ffd4c-bc10-49cd-88ca-031df07dab40', 'Stadium', '57.159814', '-2.088322', 'SRID=4326;POINT(-2.088322 57.159814)');

INSERT INTO api.slot (id, name, latitude, longitude, geog)
VALUES ('f4f8d2a6-276e-4881-875a-fc6bf4e77ea5', 'University', '57.164531', '-2.101711', 'SRID=4326;POINT(-2.101711 57.164531)');

INSERT INTO api.slot (id, name, latitude, longitude, geog)
VALUES ('5086e65c-d663-4684-8171-bf6e11bbbccb', 'Stadium Bus Stop', '57.159267', '-2.086056', 'SRID=4326;POINT(-2.086056 57.159267)');





-- Parsing rrulesets require postgres-rrule extension
-- https://github.com/volkanunsal/postgres-rrule

DROP  TABLE api.slot_avail;

CREATE TABLE api.slot_avail (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  slot_id     UUID NOT NULL REFERENCES api.slot,
  ruleset     JSON,
  duration    INTERVAL
);

GRANT SELECT ON api.slot_avail TO app_anonymous;


INSERT INTO api.slot_avail (slot_id, ruleset, duration)
VALUES ('8a45abd7-8c79-40f9-94b8-899cb3ff38fe', '[
  { "dtstart": "2020-03-01 08:00:00",
    "rrule": {"freq": "DAILY", "until": "2020-03-10 00:00:00", "wkst": "MO", "interval": 1},
    "exrule": {"freq": "WEEKLY", "until": "2020-03-10 00:00:00", "wkst": "MO", "interval": 1, "byday": "{SA,SU}"},
    "exdate": ["2020-03-10 08:00:00"]
  }
]', '12 hours');


INSERT INTO api.slot_avail (slot_id, ruleset, duration)
VALUES ('8a45abd7-8c79-40f9-94b8-899cb3ff38fe', '[
  { "dtstart": "2020-03-01 08:00:00",
    "rrule": {"freq": "DAILY", "until": "2020-03-10 00:00:00", "wkst": "MO", "interval": 1},
    "exrule": {"freq": "WEEKLY", "until": "2020-03-10 00:00:00", "wkst": "MO", "interval": 1, "byday": "{SA,SU}"},
    "exdate": ["2020-03-10 08:00:00"]
  }
]', '12 hours');

INSERT INTO api.slot_avail (slot_id, ruleset, duration)
VALUES ('7c209986-a9ae-4d33-b11d-fb72954ca00c', '[
  { "dtstart": "2020-03-01 08:00:00",
    "rrule": {"freq": "DAILY", "until": "2020-03-10 00:00:00", "wkst": "MO", "interval": 1},
    "exrule": {"freq": "WEEKLY", "until": "2020-03-10 00:00:00", "wkst": "MO", "interval": 1, "byday": "{SA,SU}"},
    "exdate": ["2020-03-10 08:00:00"]
  }
]', '12 hours');

INSERT INTO api.slot_avail (slot_id, ruleset, duration)
VALUES ('108ffd4c-bc10-49cd-88ca-031df07dab40', '[
  { "dtstart": "2020-03-01 08:00:00",
    "rrule": {"freq": "DAILY", "until": "2020-03-10 00:00:00", "wkst": "MO", "interval": 1},
    "exrule": {"freq": "WEEKLY", "until": "2020-03-10 00:00:00", "wkst": "MO", "interval": 1, "byday": "{SA,SU}"},
    "exdate": ["2020-03-10 08:00:00"]
  }
]', '12 hours');

INSERT INTO api.slot_avail (slot_id, ruleset, duration)
VALUES ('f4f8d2a6-276e-4881-875a-fc6bf4e77ea5', '[
  { "dtstart": "2020-03-01 08:00:00",
    "rrule": {"freq": "DAILY", "until": "2020-03-10 00:00:00", "wkst": "MO", "interval": 1},
    "exrule": {"freq": "WEEKLY", "until": "2020-03-10 00:00:00", "wkst": "MO", "interval": 1, "byday": "{SA,SU}"},
    "exdate": ["2020-03-10 08:00:00"]
  }
]', '12 hours');


-- Two rruleset rows for POI: Stadium Bus Stop
-- working days and duration are different in March
INSERT INTO api.slot_avail (slot_id, ruleset, duration)
VALUES ('5086e65c-d663-4684-8171-bf6e11bbbccb', '[
  { "dtstart": "2020-03-01 08:00:00",
    "rrule": {"freq": "DAILY", "until": "2020-03-10 00:00:00", "wkst": "MO", "interval": 1},
    "exrule": {"freq": "WEEKLY", "until": "2020-03-10 00:00:00", "wkst": "MO", "interval": 1, "byday": "{SA,SU}"},
    "exdate": ["2020-03-10 08:00:00"]
  }
]', '12 hours');

INSERT INTO api.slot_avail (slot_id, ruleset, duration)
VALUES ('5086e65c-d663-4684-8171-bf6e11bbbccb', '[
  { "dtstart": "2020-03-15 08:00:00",
    "rrule": {"freq": "DAILY", "until": "2020-03-25 00:00:00", "wkst": "MO", "interval": 1},
    "exrule": {"freq": "WEEKLY", "until": "2020-03-25 00:00:00", "wkst": "MO", "interval": 1, "byday": "{SA,SU}"},
    "exdate": ["2020-03-20 08:00:00"]
  }
]', '6 hours');




-- located in api schema for demo only
DROP TABLE api.slot_booking;

CREATE TABLE api.slot_booking (
  id          UUID DEFAULT uuid_generate_v4() ,
  slot_id     UUID NOT NULL REFERENCES api.slot,
  user_id     UUID NOT NULL REFERENCES api.user,
  start_time  TIMESTAMPTZ NOT NULL DEFAULT now(),
  end_time    TIMESTAMPTZ not null,
  PRIMARY KEY (slot_id, start_time)
);

CREATE INDEX ON api.slot_booking (slot_id, start_time DESC);

-- Gist index with exclude allows constraints between start_time, end_time and overlapping
ALTER TABLE api.slot_booking
  ADD CONSTRAINT no_overlapping_timestamps
  EXCLUDE USING gist( slot_id WITH =,
                      start_time WITH <>,
                      tstzrange(start_time, end_time) WITH && );


-- timescale hypertables are partitioned on `time` and with the option to partition on one or more other columns (i.e., slot or perhaps better to be city)
-- because of that, hypertables require `start_time` to be added to primary key.
-- a good approach for graphql api would be to keep this table in a separate schema, and expose queries, mutations using views and functions.
-- by that, we could mimic primary key on the view, by adding smart tag with line below where `id` is used for mutations.
-- COMMENT ON VIEW api.slot_bookings IS E'@primaryKey id';

SELECT create_hypertable('api.slot_booking', 'start_time');

GRANT SELECT ON api.slot_booking TO app_anonymous;

-- POI: Stadium Bus Stop
INSERT INTO api.slot_booking (slot_id, user_id, start_time, end_time)
VALUES ('5086e65c-d663-4684-8171-bf6e11bbbccb', 'fbc0cc53-602b-4ab6-b65a-fe8e60c57a09', '2020-03-05 09:00:00',  '2020-03-05 10:00:00'),
       ('5086e65c-d663-4684-8171-bf6e11bbbccb', 'fbc0cc53-602b-4ab6-b65a-fe8e60c57a09', '2020-03-05 11:00:00',  '2020-03-05 12:00:00'),
       ('5086e65c-d663-4684-8171-bf6e11bbbccb', 'fbc0cc53-602b-4ab6-b65a-fe8e60c57a09', '2020-03-05 15:00:00',  '2020-03-05 17:00:00');


-- POI: Stadium
INSERT INTO api.slot_booking (slot_id, user_id, start_time, end_time)
VALUES ('108ffd4c-bc10-49cd-88ca-031df07dab40', 'e4535745-ff95-4af4-bad1-9d649d506d2f', '2020-03-05 09:00:00',  '2020-03-05 10:00:00'),
       ('108ffd4c-bc10-49cd-88ca-031df07dab40', 'e4535745-ff95-4af4-bad1-9d649d506d2f', '2020-03-05 13:00:00',  '2020-03-05 14:00:00');



-- Trigger constrain due to overlap
INSERT INTO api.slot_booking (slot_id, user_id, start_time, end_time)
VALUES ('5086e65c-d663-4684-8171-bf6e11bbbccb', 'fbc0cc53-602b-4ab6-b65a-fe8e60c57a09', '2020-03-05 08:00:00',  '2020-03-05 10:00:00');

-- Trigger constrain due to end_time before start_time
INSERT INTO api.slot_booking (slot_id, user_id, start_time, end_time)
VALUES ('5086e65c-d663-4684-8171-bf6e11bbbccb', 'fbc0cc53-602b-4ab6-b65a-fe8e60c57a09', '2020-03-05 14:00:00',  '2020-03-05 13:00:00');




------------------
-- ## Timeslots


-- Get all booked timeslots for a day
SELECT *
   FROM api.slot_booking
  WHERE (start_time, end_time) OVERLAPS ('2020-03-05 08:00:00'::timestamp, '2020-03-05 20:00:00'::timestamp);

-- Get all booked timeslots for time range 8-12
SELECT *
   FROM api.slot_booking
  WHERE (start_time, end_time) OVERLAPS ('2020-03-05 08:00:00'::timestamp, '2020-03-05 12:00:00'::timestamp);



-- ### Specific parking slot_id

-- select reccuring occurences in days with duration for Stadium Bus Stop
WITH rrulesets AS (
  SELECT *
    FROM api.slot_avail AS sa
    WHERE slot_id = '5086e65c-d663-4684-8171-bf6e11bbbccb'
), rrule_occurrences AS (
  SELECT start_time
       , (start_time + rr.duration) AS end_time
       , rr.duration as duration_hours
       , EXTRACT(DOW FROM start_time) AS day_of_week
  FROM (
    SELECT
      _rrule.occurrences(
      _rrule.jsonb_to_rruleset_array(rr.ruleset::jsonb), '(,)'::TSRANGE) AS start_time
      , rr.duration
      FROM rrulesets AS rr
  ) AS rr
) SELECT * FROM rrule_occurrences;



-- all 15min timeslots avail including booked slots for Stadium Bus Stop
WITH rrulesets AS (
  SELECT *
    FROM api.slot_avail AS sa
    WHERE slot_id = '5086e65c-d663-4684-8171-bf6e11bbbccb'
), rrule_occurrences AS (
  SELECT start_time
       , (start_time + rr.duration) AS end_time
       , rr.duration as duration_hours
       , EXTRACT(DOW FROM start_time) AS day_of_week
  FROM (
    SELECT
      _rrule.occurrences(
      _rrule.jsonb_to_rruleset_array(rr.ruleset::jsonb), '(,)'::TSRANGE) AS start_time
      , rr.duration
      FROM rrulesets AS rr
  ) AS rr
), timeslots AS (
  SELECT
    ro.start_time
    , (ro.start_time + '15 minutes') AS end_time
    , ro.duration_hours
    , ro.day_of_week
  FROM (
    SELECT
      generate_series(ro.start_time::timestamp, ro.end_time::timestamp, interval '15 minutes') AS start_time
      , ro.duration_hours
      , ro.day_of_week
      FROM rrule_occurrences AS ro
  ) AS ro
), slots_in_use AS (
  SELECT DISTINCT t.start_time AS slot_start_time, t.end_time AS slot_end_time, sb.*
  FROM timeslots AS t
  JOIN api.slot_booking AS sb ON ( ((t.start_time, t.end_time) OVERLAPS (sb.start_time, sb.end_time)))
  WHERE sb.slot_id = '5086e65c-d663-4684-8171-bf6e11bbbccb'
  ORDER BY t.start_time, t.end_time

), all_timeslots AS (
  SELECT
    *
    , (
      SELECT
        slot_id
      FROM slots_in_use AS s
      WHERE x.start_time = s.slot_start_time
      AND x.end_time = s.slot_end_time
    )
    FROM timeslots as x
) SELECT * FROM all_timeslots;




-- free 15min timeslots avail excluding booked slots for Stadium Bus Stop
WITH rrulesets AS (
SELECT *
  FROM api.slot_avail AS sa
  WHERE slot_id = '5086e65c-d663-4684-8171-bf6e11bbbccb'
), rrule_occurrences AS (
SELECT start_time
     , (start_time + rr.duration) AS end_time
     , rr.duration as duration_hours
     , EXTRACT(DOW FROM start_time) AS day_of_week
FROM (
  SELECT
    _rrule.occurrences(
    _rrule.jsonb_to_rruleset_array(rr.ruleset::jsonb), '(,)'::TSRANGE) AS start_time
    , rr.duration
    FROM rrulesets AS rr
) AS rr
), timeslots AS (
SELECT
  ro.start_time
  , (ro.start_time + '15 minutes') AS end_time
  , ro.duration_hours
  , ro.day_of_week
FROM (
  SELECT
    generate_series(ro.start_time::timestamp, ro.end_time::timestamp, interval '15 minutes') AS start_time
    , ro.duration_hours
    , ro.day_of_week
    FROM rrule_occurrences AS ro
) AS ro
), slots_in_use AS (
SELECT DISTINCT t.start_time AS slot_start_time, t.end_time AS slot_end_time, sb.*
FROM timeslots AS t
JOIN api.slot_booking AS sb ON ( ((t.start_time, t.end_time) OVERLAPS (sb.start_time, sb.end_time)))
WHERE sb.slot_id = '5086e65c-d663-4684-8171-bf6e11bbbccb'
ORDER BY t.start_time, t.end_time

), free_timeslots AS (
  SELECT
    * FROM timeslots as x
  WHERE NOT EXISTS (
    SELECT 1
    FROM slots_in_use AS s
    WHERE x.start_time = s.slot_start_time
    AND x.end_time = s.slot_end_time
  )
  ORDER BY x.start_time, x.end_time
) SELECT * FROM free_timeslots;



-- ### All parking slots

-- free 15min timeslots avail excluding booked slots for all parking slots
WITH rrulesets AS (
SELECT *
  FROM api.slot_avail AS sa
), rrule_occurrences AS (
SELECT start_time
     , (start_time + rr.duration) AS end_time
     , rr.duration as duration_hours
     , EXTRACT(DOW FROM start_time) AS day_of_week
     , rr.slot_id
FROM (
  SELECT
    _rrule.occurrences(
    _rrule.jsonb_to_rruleset_array(rr.ruleset::jsonb), '(,)'::TSRANGE) AS start_time
    , rr.duration
    , rr.slot_id
    FROM rrulesets AS rr
) AS rr
), timeslots AS (
SELECT
  ro.start_time
  , (ro.start_time + '15 minutes') AS end_time
  , ro.duration_hours
  , ro.day_of_week
  , ro.slot_id
FROM (
  SELECT
    generate_series(ro.start_time::timestamp, ro.end_time::timestamp, interval '15 minutes') AS start_time
    , ro.duration_hours
    , ro.day_of_week
    , ro.slot_id
    FROM rrule_occurrences AS ro
) AS ro
), slots_in_use AS (
SELECT DISTINCT t.start_time AS slot_start_time, t.end_time AS slot_end_time, sb.*
FROM timeslots AS t
JOIN api.slot_booking AS sb ON ( ((t.start_time, t.end_time) OVERLAPS (sb.start_time, sb.end_time)))
WHERE sb.slot_id = t.slot_id
ORDER BY t.start_time, t.end_time

), free_timeslots AS (
  SELECT
    * FROM timeslots as x
  WHERE NOT EXISTS (
    SELECT 1
    FROM slots_in_use AS s
    WHERE x.start_time = s.slot_start_time
    AND x.end_time = s.slot_end_time
    AND x.slot_id = s.slot_id
  )
  ORDER BY x.start_time, x.end_time
) SELECT * FROM free_timeslots;




-- ### Slots in distance
-- ### Point golf club ()-2.084919, 57.158675)

-- free timeslots avail excluding booked slots for all parking slots within 500m near golf club / no time range

WITH slots_in_distance AS (
  SELECT
    sp.*
    , ST_Distance(sp.geog, ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326)) AS distance_4326
  FROM api.slot AS sp
  WHERE ST_DWithin(sp.geog, ST_Point(-2.084919, 57.158675)::geography, 500)
  ORDER BY sp.geog::geometry <-> ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326)
  LIMIT 10
), rrulesets AS (
SELECT
  sa.*
  , sid.*
  FROM api.slot_avail AS sa
  JOIN slots_in_distance AS sid ON sa.slot_id = sid.id
), rrule_occurrences AS (
SELECT start_time
     , (start_time + rr.duration) AS end_time
     , rr.duration as duration_hours
     , EXTRACT(DOW FROM start_time) AS day_of_week
     , rr.slot_id
     , rr.name
FROM (
  SELECT
    _rrule.occurrences(
    _rrule.jsonb_to_rruleset_array(rr.ruleset::jsonb), '(,)'::TSRANGE) AS start_time
    , rr.duration
    , rr.slot_id
    , rr.name
    FROM rrulesets AS rr
) AS rr
), timeslots AS (
SELECT
  ro.start_time
  , (ro.start_time + '15 minutes') AS end_time
  , ro.duration_hours
  , ro.day_of_week
  , ro.slot_id
  , ro.name
FROM (
  SELECT
    generate_series(ro.start_time::timestamp, ro.end_time::timestamp, interval '15 minutes') AS start_time
    , ro.duration_hours
    , ro.day_of_week
    , ro.slot_id
    , ro.name
    FROM rrule_occurrences AS ro
) AS ro
), slots_in_use AS (
SELECT DISTINCT t.start_time AS slot_start_time, t.end_time AS slot_end_time, sb.*
FROM timeslots AS t
JOIN api.slot_booking AS sb ON ( ((t.start_time, t.end_time) OVERLAPS (sb.start_time, sb.end_time)))
WHERE sb.slot_id = t.slot_id
ORDER BY t.start_time, t.end_time

), free_timeslots AS (
  SELECT
    * FROM timeslots as x
  WHERE NOT EXISTS (
    SELECT 1
    FROM slots_in_use AS s
    WHERE x.start_time = s.slot_start_time
    AND x.end_time = s.slot_end_time
    AND x.slot_id = s.slot_id
  )
  ORDER BY x.start_time, x.end_time
) SELECT * FROM free_timeslots;


-- ## With time range

-- free timeslots avail excluding booked slots for all parking slots within 500m near golf club between 8 and 12

WITH slots_in_distance AS (
  SELECT
    sp.*
    , ST_Distance(sp.geog, ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326)) AS distance_4326
  FROM api.slot AS sp
  WHERE ST_DWithin(sp.geog, ST_Point(-2.084919, 57.158675)::geography, 500)
  ORDER BY sp.geog::geometry <-> ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326)
  LIMIT 10
), rrulesets AS (
SELECT
  sa.*
  , sid.*
  FROM api.slot_avail AS sa
  JOIN slots_in_distance AS sid ON sa.slot_id = sid.id
), rrule_occurrences AS (
SELECT start_time
  , (start_time + rr.duration) AS end_time
  , EXTRACT(DOW FROM start_time) AS day_of_week
  , rr.slot_id
  , rr.name
  FROM (
    SELECT
      _rrule.occurrences(
      _rrule.jsonb_to_rruleset_array(rr.ruleset::jsonb), '(,)'::TSRANGE) AS start_time
      , rr.duration
      , rr.slot_id
      , rr.name
      FROM rrulesets AS rr
  ) AS rr
  WHERE CAST(rr.start_time AS DATE) = CAST('2020-03-05 08:00:00' AS DATE)
), timeslots AS (
SELECT
  ro.start_time
  , (ro.start_time + '15 minutes') AS end_time
  , ro.day_of_week
  , ro.slot_id
  , ro.name
  FROM (
    SELECT
      generate_series(ro.start_time::timestamp, ro.end_time::timestamp, interval '15 minutes') AS start_time
      , ro.day_of_week
      , ro.slot_id
      , ro.name
      FROM rrule_occurrences AS ro
  ) AS ro
  WHERE ro.start_time::timestamp >= '2020-03-05 08:00:00'
    AND (ro.start_time + '15 minutes')::timestamp <=  '2020-03-05 12:00:00'
), slots_in_use AS (
SELECT DISTINCT t.start_time AS slot_start_time, t.end_time AS slot_end_time, sb.*
FROM timeslots AS t
JOIN api.slot_booking AS sb ON ( ((t.start_time, t.end_time) OVERLAPS (sb.start_time, sb.end_time)))
WHERE sb.slot_id = t.slot_id
ORDER BY t.start_time, t.end_time

), free_timeslots AS (
  SELECT
    * FROM timeslots as x
  WHERE NOT EXISTS (
    SELECT 1
    FROM slots_in_use AS s
    WHERE x.start_time = s.slot_start_time
    AND x.end_time = s.slot_end_time
    AND x.slot_id = s.slot_id
  )
  ORDER BY x.name, x.start_time, x.end_time
) SELECT * FROM free_timeslots;




-- all timeslots avail including booked slots for all parking slots within 500m near golf club between 8 and 12AM

WITH slots_in_distance AS (
  SELECT
    sp.*
    , ST_Distance(sp.geog, ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326)) AS distance_4326
  FROM api.slot AS sp
  WHERE ST_DWithin(sp.geog, ST_Point(-2.084919, 57.158675)::geography, 500)
  ORDER BY sp.geog::geometry <-> ST_SetSRID(ST_MakePoint(-2.084919, 57.158675),4326)
  LIMIT 10
), rrulesets AS (
SELECT
  sa.*
  , sid.*
  FROM api.slot_avail AS sa
  JOIN slots_in_distance AS sid ON sa.slot_id = sid.id
), rrule_occurrences AS (
SELECT start_time
  , (start_time + rr.duration) AS end_time
  , EXTRACT(DOW FROM start_time) AS day_of_week
  , rr.slot_id
  , rr.name
  FROM (
    SELECT
      _rrule.occurrences(
      _rrule.jsonb_to_rruleset_array(rr.ruleset::jsonb), '(,)'::TSRANGE) AS start_time
      , rr.duration
      , rr.slot_id
      , rr.name
      FROM rrulesets AS rr
  ) AS rr
  WHERE CAST(rr.start_time AS DATE) = CAST('2020-03-05 08:00:00' AS DATE)
), timeslots AS (
SELECT
  ro.start_time
  , (ro.start_time + '15 minutes') AS end_time
  , ro.day_of_week
  , ro.slot_id
  , ro.name
  FROM (
    SELECT
      generate_series(ro.start_time::timestamp, ro.end_time::timestamp, interval '15 minutes') AS start_time
      , ro.day_of_week
      , ro.slot_id
      , ro.name
      FROM rrule_occurrences AS ro
  ) AS ro
  WHERE ro.start_time::timestamp >= '2020-03-05 08:00:00'
    AND (ro.start_time + '15 minutes')::timestamp <=  '2020-03-05 12:00:00'
), slots_in_use AS (
SELECT DISTINCT t.start_time AS slot_start_time, t.end_time AS slot_end_time, sb.*
FROM timeslots AS t
JOIN api.slot_booking AS sb ON ( ((t.start_time, t.end_time) OVERLAPS (sb.start_time, sb.end_time)))
WHERE sb.slot_id = t.slot_id
ORDER BY t.start_time, t.end_time

), all_timeslots AS (
  SELECT
    *
    , (
      SELECT
        s.id AS slot_booking_id
      FROM slots_in_use AS s
      WHERE x.start_time = s.slot_start_time
      AND x.end_time = s.slot_end_time
      AND x.slot_id = s.slot_id
    )
    FROM timeslots as x
    ORDER BY x.name, x.start_time, x.end_time
) SELECT * FROM all_timeslots;





------------------
-- ## Time Buckets

-- Report booked timeslots in last 10 days / no gap
SELECT
  time_bucket('15 minutes', start_time) AS fifteen_min
  , sb.*
  FROM api.slot_booking AS sb
  WHERE start_time > NOW() - interval '10 days'
  GROUP BY fifteen_min, sb.slot_id, sb.start_time
  ORDER BY fifteen_min DESC;


-- Report booked timeslots in last 10 days / with gap for time range 8-20
SELECT
  time_bucket_gapfill('15 minutes', start_time, '2020-03-05 08:00:00', '2020-03-05 20:00:00') AS fifteen_min
  , count(*) as reserved
  FROM api.slot_booking AS sb
  WHERE start_time > NOW() - interval '10 days'
  GROUP BY fifteen_min
  ORDER BY fifteen_min DESC;


-- Report time bucket per 1 hour for booked timeslots in last 10 days / with gap for time range 8-20
SELECT
  time_bucket_gapfill('1 hour', start_time, '2020-03-05 08:00:00', '2020-03-05 20:00:00') AS day
  , count(*) as reserved
  FROM api.slot_booking AS sb
  WHERE start_time > NOW() - interval '10 days'
  GROUP BY day
  ORDER BY day DESC;


-- Report time bucket per 1 hour for booked timeslots in last 10 days / with gap for 5 days
SELECT
  time_bucket_gapfill('1 hour', start_time, '2020-03-03 08:00:00', '2020-03-08 20:00:00') AS day
  , count(*) as reserved
  FROM api.slot_booking AS sb
  WHERE start_time > NOW() - interval '10 days'
  GROUP BY day
  ORDER BY day DESC;


-- Report time bucket per day for booked timeslots in last 10 days / with gap for 5 days
SELECT
  time_bucket_gapfill('1 day', start_time, '2020-03-03 08:00:00', '2020-03-08 20:00:00') AS day
  , count(*) as reserved
  FROM api.slot_booking AS sb
  WHERE start_time > NOW() - interval '10 days'
  GROUP BY day
  ORDER BY day DESC;