1

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?

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.