I have table with millions of companies data (
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?
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.