insert.sql

-- 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.sql

-- 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))

scheme.sql

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;

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).