Scheduling with Date Mod Part 1

One question that seems to come up a lot is how to model recurring schedules. That’s understandable, because schedules can get out of hand pretty quickly.

We need a template so that we can define a schedule and then some how project it out on a repeating basis. The single week schedules are pretty easy. We just record the day of week and for each schedule and day we look up that day of week to get the shift for that day.

But life is rarely that simple. Chances are that some employees will be working on a one week cycle while others are on a two or four (or more) week schedule.

This tutorial will be using data types for Postgres, but the concepts here can be applied to other databases. That said, we’ll be using tools provided by Chronos, a temporal toolkit for Oracle and Postgres. So if you don’t have that, you’ll have to do some of the heavy lifting yourself.

So assuming you’ve got Chronos already installed, lets get started. The first thing we need is a calendar table. In data warehousing, we almost always have a date dimension table. It is less common to find calendar tables in OLTP databases. But for any kind of scheduling application, a calendar table is invaluable.


CREATE TABLE info_calendar(
cal_date DATE PRIMARY KEY,
cal_period PERIOD,
wk_idx INT2,
wk_nidx INT2
);
--
-- Populate it from 2005 to 2015
-- You'll want a wider range for an actual application
--
INSERT INTO info_calendar (cal_date, cal_period, wk_idx, wk_nidx)
SELECT dt, period(dt), week_of_month(dt, 1), week_of_month(dt, -1)
FROM (
SELECT DATE '2005-01-01' + i AS dt
FROM generate_series(0, 3652) i
) sub;


So a couple things to point out here. One is to point out that a date is a special type of period that always starts at midnight and is 24 hours long. So we will convert each date to a period.

We used the week_of_month() function from Chronos to get week index from the beginning and the end of the month. We wont use it in this tutorial, but later we’ll use so we can schedule recurring meetings on the 3rd Tuesday of the month. For now lets just run a sample query to find all of the Thanksgivings in our calendar. Thanksgiving (in the US) is the 4th Thursday of November.

SELECT c.cal_date
FROM info_calendar c
WHERE EXTRACT(MONTH FROM cal_date) = 11
AND EXTRACT(DOW FROM cal_date) = 4
AND wk_idx = 4;


To be able to model these advanced schedules we’ll need to know how often they repeat, and we need an anchor date. In the next article, we’ll see how to put these pieces together.