Skip to content

Instantly share code, notes, and snippets.

@lukaspili
Last active March 5, 2022 17:39
Show Gist options
  • Save lukaspili/0fadc6487efce6c0b03e80d9bcfe8d1e to your computer and use it in GitHub Desktop.
Save lukaspili/0fadc6487efce6c0b03e80d9bcfe8d1e to your computer and use it in GitHub Desktop.
-- insert a new place and associated hours
create function app_public.save_place(
city_id text,
address text,
latitude double precision,
longitude double precision,
hours tsrange[],
phone app_public.phone_number default null,
) returns app_public.places as $$
declare
v_place app_public.places;
v_geography geography := ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
begin
-- insert place
insert into app_public.places (city_id, address, geography, phone)
values (city_id, address, v_geography, phone)
returning * into v_place;
-- insert hours
-- it calls app_public.place_hours_range() to create the range from the tsrange[],
-- which can return 1 or 2 ranges, so it then uses lateral join for insert
insert into app_public.place_hours(place_id, hours)
select id, insert_hours
from (
select v_place.id, lower(unnest(save_place.hours)), upper(unnest(save_place.hours))
) t(id, f, t), app_public.place_hours_range(f, t) insert_hours
on conflict on constraint place_hours_pkey do nothing;
return v_place;
end;
$$ language plpgsql volatile;
-- query the places that are open at a specific time, defined by param: only_open_at timestamp
select distinct on (p.id) p.*
from app_public.published_places() p
left join app_public.place_hours ph on ph.place_id = p.id
where (only_open_at is null or ph.hours @> app_public.place_hours_time((only_open_at + interval '30 minute')::timestamp))
create table app_public.place_hours (
place_id bigint not null references app_public.places on delete cascade,
hours tsrange not null,
primary key(place_id, hours),
constraint place_hours_no_overlap exclude using gist (place_id with =, hours with &&),
constraint place_hours_bounds_inclusive check (lower_inc(hours) and upper_inc(hours)),
constraint place_hours_standard_week check (hours <@ tsrange '[1996-01-01 0:0, 1996-01-08 0:0]'),
constraint place_hours_no_3days_range check (date_part('days', upper(hours) - lower(hours)) < 2)
);
create index on app_public.place_hours(place_id);
create index place_hours_hours_spgist_idx on app_public.place_hours USING spgist (hours);
create or replace function app_public.place_hours_time(timestamp)
returns timestamp AS
$func$
select date '1996-01-01' + ($1 - date_trunc('week', $1))
$func$ language sql immutable;
create or replace function app_public.place_hours_range(_from timestamp, _to timestamp)
return table (place_hours tsrange) AS
$func$
declare
ts_from timestamp := app_public.place_hours_time(_from);
ts_to timestamp := app_public.place_hours_time(_to);
begin
IF _to <= _from THEN
RAISE EXCEPTION '%', '_to must be later than _from!';
ELSIF _to > _from + interval '1 week' THEN
RAISE EXCEPTION '%', 'Interval cannot span more than a week!';
-- elsif ts_to > ts
END IF;
-- if range overlaps on two weeks, create two ranges by splitting at monday 00:00
IF ts_from > ts_to THEN
RETURN QUERY
VALUES (tsrange('1996-01-01 0:0', ts_to , '[]'))
, (tsrange(ts_from, '1996-01-08 0:0', '[]'));
-- otherwise
ELSE
place_hours := tsrange(ts_from, ts_to, '[]');
RETURN NEXT;
END IF;
RETURN;
END
$func$ language plpgsql immutable cost 1000 rows 1;
@lukaspili
Copy link
Author

The idea is to define a tsrange for each opening range.
It allows to have opening hours which overlap between several days, for a complete week.

However there is one special case, for opening hours that overlap between 2 weeks (sunday 22:00 to monday 02:00 for example). In that case, we split the range in two ranges (sunday 22:00 to 23:59 and monday 00:00 to 02:00). That's the role of the helper function place_hours_range.

The second helper place_hours_time is to just use the day of week + time from a timestamp (we ignore the year).

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment