Storing companies' working hours in Postgres

Question

I have table with millions of companies data (name, address, phones etc) in my Postgres database. I want to store working hours (example: mon-fri: 9:00-12:00, 13:00-18:00; sat: 9:00-13:00). How can I store it to be able to find all companies opened in requested time?

Answer 1

I would store the opening hours per weekday/per company. For storing the actual hours, a timerange seems suitable.

create table opening_hours
(
  company_id int not null references company,
  day_of_week int not null, -- 1 = Monday
  hours timerange not null
);

I would use 1 for Monday because that's the ISO definition and most of Postgres' date functions work like that, so you don't need to do conversion from and to a different base number for Monday.

If there is a noon break, it would mean that the day_of_week gets two entries, e.g.

insert into opening_hours (company_id, day_of_week, hours)
values
(1, 1, timerange('09:00', '12:00')),
(1, 1, timerange('13:00', '18:00'));

To find the companies that are opened at a specific day and time you can use something like this:

select *
from company c
where exists (select *
              from opening_hours oh
              where oh.company_id = c.id
                and oh.hours @> time '17:35'
                and oh.day_of_week = 2)

An exclusion constraint can be used to avoid overlapping opening hours for the same day and company.