Calculate amount of date ranges' overlaps

user_3568719 0
Viewed 50

I try to store availability hours for different services. (Every work days (8, 9 hours), 24/7 etc.) A work day can have different office times (08:00-16:00 or 09:00-17:00, 08:00-18:00 etc.)

There are also national holidays where a normal work day acts like a weekend, and any given weekend day can be also work day.

What is the best way to store this in a database (PostgreSQL)?

Background

The main goal is to calculate if how many minutes of a given time range is in service time.

A) A service time is only work days (08:00-16:00) and the date range is [2019-10-21 15:00 (Monday) - 2019-10-22 09:00 (Tuesday)]. The whole range is 18 hours long, but only 2 hours were in service time.

B) I also want to calculate if I know the start time (2019-10-21 15:00) and there is 3 hours to finish the work (in work time) then when will be the end date? (2019-10-22 10:00 - 1 hour on Monday, 2 hours on Tuesday)

momentjs node.js postgresql database
1 Answers

I would store the data exactly as how you described it.

You can have a table for work windows, i.e. Mon-Fri 08:00 to 16:00

And each service will have it's date ranges, start and stop. I'm guess a service will have multiple date ranges so you might need a date range table that has dateRangeId, start & stop columns.

When you want to calculate something, you pull data for the service and pull data for the work days and calculate what is the actual service time and any other questions you need answered :)

user_2171970
Related