Skip to content

Instantly share code, notes, and snippets.

@kachar

kachar/gist:0f787af108607a309c1c0858d87b34a2

Forked from svilensabev/gist:713584cd30133969f395d1333563031e
Last active August 25, 2022 07:48
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save kachar/0f787af108607a309c1c0858d87b34a2 to your computer and use it in GitHub Desktop.
Save kachar/0f787af108607a309c1c0858d87b34a2 to your computer and use it in GitHub Desktop.
Download ZIP
PostgreSQL timeslots, rrulesets and booking timetables
Raw
gistfile1.sql
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment