Store the day of the week and time?

Question

I have a two-part question about storing days of the week and time in a database. I'm using Rails 4.0, Ruby 2.0.0, and Postgres.

I have certain events, and those events have a schedule. For the event "Skydiving", for example, I might have Tuesday and Wednesday and 3 pm.

1. Is there a way for me to store the record for Tuesday and Wednesday in one row or should I have two records?
2. What is the best way to store the day and time? Is there a way to store day of week and time (not datetime) or should these be separate columns? If they should be separate, how would I store the day of the week? I was thinking of storing them as integer values, 0 for Sunday, 1 for Monday, since that's how the wday method for the Time class does it.

Any suggestions would be super helpful.

Answer 1

Is there a way for me to store the the record for Tuesday and Wednesday in one row or do should I have two records?

There are several ways to store multiple time ranges in a single row. @bma already provided a couple of them. That might be useful to save disk space with very simple time patterns. The clean, flexible and "normalized" approach is to store one row per time range.

What is the best way to store the day and time?

Use a timestamp (or timestamptz if multiple time zones may be involved). Pick an arbitrary "staging" week and just ignore the date part while using the day and time aspect of the timestamp. Simplest and fastest in my experience, and all date and time related sanity-checks are built-in automatically. I use a range starting with 1996-01-01 00:00 for several similar applications for two reasons:

• The first 7 days of the week coincide with the day of the month (for sun = 7).
• It's the most recent leap year (providing Feb. 29 for yearly patterns) at the same time.

Range type

Since you are actually dealing with time ranges (not just "day and time") I suggest to use the built-in range type tsrange (or tstzrange). A major advantage: you can use the arsenal of built-in Range Functions and Operators. Requires Postgres 9.2 or later.

For instance, you can have an exclusion constraint building on that (implemented internally by way of a fully functional GiST index that may provide additional benefit), to rule out overlapping time ranges. Consider this related answer for details:

• Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

For this particular exclusion constraint (no overlapping ranges per event), you need to include the integer column event_id in the constraint, so you need to install the additional module btree_gist. Install once per database with:

CREATE EXTENSION btree_gist;  -- once per db

Or you can have one simple CHECK constraint to restrict the allowed time period using the "range is contained by" operator <@.

Could look like this:

CREATE TABLE event (event_id serial PRIMARY KEY, ...);

CREATE TABLE schedule (
   event_id integer NOT NULL REFERENCES event(event_id)
                    ON DELETE CASCADE ON UPDATE CASCADE
 , t_range  tsrange
 , PRIMARY KEY (event_id, t_range)
 , CHECK (t_range <@ '[1996-01-01 00:00, 1996-01-09 00:00)')  -- restrict period
 , EXCLUDE USING gist (event_id WITH =, t_range WITH &&)      -- disallow overlap
);

For a weekly schedule use the first seven days, Mon-Sun, or whatever suits you. Monthly or yearly schedules in a similar fashion.

How to extract day of week, time, etc?

@CDub provided a module to deal with it on the Ruby end. I can't comment on that, but you can do everything in Postgres as well, with impeccable performance.

SELECT ts::time AS t_time           -- get the time (practically no cost)
SELECT EXTRACT(DOW FROM ts) AS dow  -- get day of week (very cheap)

Or in similar fashion for range types:

SELECT EXTRACT(DOW FROM lower(t_range)) AS dow_from  -- day of week lower bound
     , EXTRACT(DOW FROM upper(t_range)) AS dow_to    -- same for upper
     , lower(t_range)::time AS time_from             -- start time
     , upper(t_range)::time AS time_to               -- end time
FROM   schedule;

db<>fiddle here
_{Old sqliddle}

ISODOW instead of DOW for EXTRACT() returns 7 instead of 0 for sundays. There is a long list of what you can extract.

This related answer demonstrates how to use range type operator to compute a total duration for time ranges (last chapter):

• Calculate working hours between 2 dates in PostgreSQL

Answer 2

Check out the ice_cube gem (link).

It can create a schedule object for you which you can persist to your database. You need not create two separate records. For the second part, you can create schedule based on any rule and you need not worry on how that will be saved in the database. You can use the methods provided by the gem to get whatever information you want from the persisted schedule object.

Answer 3

Depending how complex your scheduling needs are, you might want to have a look at RFC 5545, the iCalendar scheduling data format, for ideas on how to store the data.

If you needs are pretty simple, than that is probably overkill. Postgresql has many functions to convert date and time to whatever format you need.

For a simple way to store relative dates and times, you could store the day of week as an integer as you suggested, and the time as a TIME datatype. If you can have multiple days of the week that are valid, you might want to use an ARRAY.

Eg.

• ARRAY[2,3]::INTEGER[] = Tues, Wed as Day of Week
• '15:00:00'::TIME = 3pm

[EDIT: Add some simple examples]

/* Custom the time and timetz range types */
CREATE TYPE timerange AS RANGE (subtype = time);

--drop table if exists schedule;
create table schedule (
    event_id    integer not null, /* should be an FK to "events" table */
    day_of_week integer[],
    time_of_day time,
    time_range  timerange,
    recurring   text CHECK (recurring IN ('DAILY','WEEKLY','MONTHLY','YEARLY'))
);

insert into schedule (event_id, day_of_week, time_of_day, time_range, recurring)
values
(1, ARRAY[1,2,3,4,5]::INTEGER[], '15:00:00'::TIME, NULL, 'WEEKLY'),
(2, ARRAY[6,0]::INTEGER[], NULL, '(08:00:00,17:00:00]'::timerange, 'WEEKLY');

select * from schedule;

 event_id | day_of_week | time_of_day |     time_range      | recurring 
----------+-------------+-------------+---------------------+-----------
        1 | {1,2,3,4,5} | 15:00:00    |                     | WEEKLY
        2 | {6,0}       |             | (08:00:00,17:00:00] | WEEKLY

The first entry could be read as: the event is valid at 3pm Mon - Fri, with this schedule occurring every week.
The second entry could be read as: the event is valid Saturday and Sunday between 8am and 5pm, occurring every week.

The custom range type "timerange" is used to denote the lower and upper boundaries of your time range.
The '(' means "inclusive", and the trailing ']' means "exclusive", or in other words "greater than or equal to 8am and less than 5pm".

Answer 4

Why not just store the datestamp then use the built in functionality for Date to get the day of the week?

2.0.0p247 :139 > Date.today
 => Sun, 10 Nov 2013 
2.0.0p247 :140 > Date.today.strftime("%A")
 => "Sunday" 

strftime sounds like it can do everything for you. Here are the specific docs for it.

Specifically for what you're talking about, it sounds like you'd need an Event table that has_many :schedules, where a Schedule would have a start_date timestamp...