Save 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 use Rails 4.0, Ruby 2.0.0 and Postgres.

I have certain events, and these events have a schedule. For example, for the Skydiving event, I could have Tuesday and Wednesday and 3 p.m.

• Is there a way to keep the entry on Tuesday and Wednesday on the same line, or do I need to have two entries?
• What is the best way to keep the day and time? Is there a way to store the day of the week and time (rather than datetime), or should it be separate columns? If they should be separate, how will I keep the day of the week? I thought of storing them as integer values, 0 for Sunday, 1 for Monday, as the wday method wday for the Time class.

Any suggestions would be very helpful.

Answer 1

Is there a way to keep the entry on Tuesday and Wednesday on the same line, or do I have two entries?

There are several ways to store multiple time ranges on a single line. @bma has already provided a couple of them. This can be useful to save disk space with very simple temporary patterns. A clean, flexible, and “normalized” approach is to save one line per time range .

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

Use timestamp (or timestamptz if you have to deal with multiple time zones). Choose an arbitrary "interim" week and simply ignore the portion of the date using the day and time aspects of the timestamp . The simplest and fastest in my experience, and all date and time related health checks are built-in automatically. I use a range starting from 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 ).
• This is the last leap year (available February 29 for annual models) at the same time.

Range type

Since you are actually dealing with time ranges (not just day and time), I suggest using the built-in range type tsrange (or tstzrange ). The main advantage: you can use the arsenal of built-in range functions and operators . Requires Postgres 9.2 or later .

For example, an exception restriction restriction can be built on it (implemented internally using the fully functional GiST index, which can provide additional benefits) to eliminate overlapping time ranges. Consider this answer:

• Prevent contiguous / overlapping records with EXCLUDE in PostgreSQL

For this specific exception constraint (without overlapping ranges for each event), you need to include the integer event_id column in the constraint, so you need to install the additional btree_gist module. Install once for each database using:

 CREATE EXTENSION btree_gist; -- once per db 

Or you may have one simple CHECK constraint to limit the allowed time period using the "range contained" operator <@ .

It might 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 annual schedules in the same way.

How to extract day of the week, time, etc.

@CDub provided a module to handle this at the end of Ruby. I cannot comment on this, but you can also do everything in Postgres, with flawless 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 similarly 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; 

SQL Fiddle

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

This answer shows how to use the range type operator to calculate the total duration for time ranges (last chapter):

• Calculate working hours between two dates in PostgreSQL

Answer 2

Check out the ice_cube pearl ( link ).

He can create a schedule object for you, which you can save in your database. You do not need to create two separate records. In the second part, you can create a schedule based on any rule, and you do not need to worry about how it will be stored in the database. You can use the methods provided by the gem to retrieve any information you want from the object of the saved schedule.

Answer 3

Depending on how complex your planning needs are, you can look at the RFC 5545 , iCalendar data format schedule for ideas on how to store data.

If you need quite simply than this is probably bust. Postgresql has many features to convert date and time to any format you need.

For an easy way to store relative dates and times, you can save the day of the week as an integer, as you said, and time as a TIME data type. If you can use several days of the week, you can use ARRAY.

Eg.

• ARRAY [2,3] :: INTEGER [] = Tue, Wed like day of the 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 can be read as: the event is valid at 3 pm Monday through Friday, and this schedule occurs every week.
The second entry can be read as: the event is valid on Saturday and Sunday between 8 am and 5 pm, which occur every week.

The user-defined range type "timer" is used to indicate the lower and upper bounds of your time range.
"(" Means "inclusive", and the final "]" means "exceptional", or, in other words, "greater than or equal to 8 mornings and less than 5 pm."

Answer 4

Why not just save the datestamp and then use the built-in functions 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 he can do everything for you. Specific documents for him are listed here .

In particular, for what you're talking about, it looks like you will need an Event table that has_many :schedules , where Schedule will have a start_date ...