SQL - modeling overlapping schedules

Questions / Help
1

I’m working an a telephony application in Elixir.

Right now, I’m trying to figure out the optimal model for (call) routing schedules. I want to make Postgres do the work of matching today’s date, the time, and the day. Ranges, I think, are a perfect use case for this situation. The <@ operator allows us to check if the date or time is an element of the range. We can make default/fallback rules that apply to any time range, any date range, or any day by giving each respective field a NULL value.

As an example, I have a client who has a few different schedules:

  • Monday through Friday, 9AM-5PM
    • Lunch 12noon-1PM
  • After hours

Here’s the model I have so far:

CREATE TYPE timerange AS range(subtype = time);

CREATE TABLE rules (
       id SERIAL NOT NULL PRIMARY KEY,
       date_range daterange,
       time_range timerange,
       week_day text[],
       cardinality text -- not using cardinality right now
);

CREATE FUNCTION daterange_diff(daterange) RETURNS integer
       AS 'SELECT UPPER($1) - LOWER($1);'
       LANGUAGE SQL
       IMMUTABLE
       RETURNS NULL ON NULL INPUT;

CREATE FUNCTION timerange_diff(timerange) RETURNS interval
       AS 'SELECT UPPER($1) - LOWER($1);'
       LANGUAGE SQL
       IMMUTABLE
       RETURNS NULL ON NULL INPUT;

Why not use tsrange? Well, there would be no way to specify the exact times without having to create a bunch of rows. There would probably be a bunch of edge cases as well. Using a daterange means there is more flexibility. Then we have a time_range with the custom type timerange to supply a rule specific to a particular time of day.

Finally, we have a week_day field which is an array that should supply the days of the week that the rule applies to. I plan on using cardinality later so I can have rules like “second Friday of the month.”

Nested Rules

An important aspect of this model is being able to nest rules. Like the above example, we want to match on “Monday through Friday, 9AM-5PM” and “Lunch 12noon-1PM”. A naïve way to do this would be creating three rules. One that matches “Monday through Friday, 9AM-12noon”, “Monday through Friday, 12noon-1PM” and “Monday through Friday, 1PM-5PM”. That seems too complex. The application will have to do a lot of extra work to calculate the splits and it will result in more rows.

So I came up with this:

WITH matched_rules AS (
     SELECT id, time_range, date_range FROM rules
     -- find rows with localtime UTC within time_range
     WHERE ((SELECT localtime) <@ time_range)
     -- find rows with current_date within time_range
     AND ((SELECT current_date) <@ date_range)
     -- find the current week day within the week_day array
     AND (('{friday}' <@ week_day OR (week_day IS NULL)))
     ), candidate_rule AS(
     SELECT id FROM matched_rules
     -- find the smallest range that matches 
     -- or a default (null range) for both time_range and date_range
     WHERE ((timerange_diff(time_range) = (SELECT min(timerange_diff(time_range)) FROM matched_rules)) OR (time_range = '(,)'::timerange)) AND
           ((daterange_diff(date_range) = (SELECT min(daterange_diff(date_range)) FROM matched_rules)) OR (date_range = '(,)'::daterange))
     )
SELECT * FROM rules WHERE id IN (SELECT id FROM candidate_rule);

The test data:

INSERT INTO rules(date_range, time_range, week_day)
VALUES
('(,)'::daterange, timerange('13:00'::time, '21:00'::time, '[]'), ARRAY ['monday', 'tuesday', 'wednesday', 'thursday', 'friday']),
('(,)'::daterange, timerange('16:00'::time, '17:00'::time, '[]'), ARRAY ['monday', 'tuesday', 'wednesday', 'thursday', 'friday']),
('(,)'::daterange, '(,)'::timerange, NULL);

So now, we can select '16:30'::time on Friday and we get back:

 id | date_range |     time_range      |                  week_day                  
----+------------+---------------------+--------------------------------------------
  2 | (,)        | [16:00:00,17:00:00] | {monday,tuesday,wednesday,thursday,friday}
  3 | (,)        | (,)                 |

Nice!

Now, how about a time between 13:00-21:00 on Friday? Let’s try '18:00'::time:

 id | date_range |     time_range      |                  week_day                  
----+------------+---------------------+--------------------------------------------
  1 | (,)        | [13:00:00,21:00:00] | {monday,tuesday,wednesday,thursday,friday}
  3 | (,)        | (,)                 |

Awww yesss.

Problems?

So I wanted to know if anyone could see any problems with this model. Obviously, the selections will always return the default rule (the one that matches any date_time or any date_range), but I intend on writing application code that will resolve and prioritize the more specific rules if any are returned.