Store the day of the week and time?

by

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:

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):

More Related Contents:

  1. How to handle Ruby on Rails error: “Please install the postgresql adapter: `gem install activerecord-postgresql-adapter'”
  2. PG::ConnectionBad – could not connect to server: Connection refused
  3. ActiveRecord.find(array_of_ids), preserving order
  4. Rails model without database
  5. How do I replace accented Latin characters in Ruby?
  6. Using Rails serialize to save hash to database
  7. Rails 3: Get Random Record
  8. Altering the primary key in Rails to be a string
  9. LEFT OUTER joins in Rails 3
  10. Rails: ActiveRecord query based on association value
  11. Rails 3, has_one / has_many with lambda condition
  12. How do i work with two different databases in rails with active records?
  13. Rails ActiveRecord: Find All Users Except Current User
  14. GroupingError: ERROR: column must appear in the GROUP BY clause or be used in an aggregate function
  15. query , can not select column count
  16. How to use ActiveRecord in a ruby script outside Rails?
  17. Creating a PostgreSQL sequence to a field (which is not the ID of the record)
  18. Can I write PostgreSQL functions on Ruby on Rails?
  19. Regular expressions with validations in RoR 4
  20. Using helpers in model: how do I include helper dependencies?
  21. delete_all vs destroy_all?
  22. Rails 4 LIKE query – ActiveRecord adds quotes
  23. Converting an array of objects to ActiveRecord::Relation
  24. Rails 3.1 limit user created objects
  25. PGError: ERROR: aggregates not allowed in WHERE clause on a AR query of an object and its has_many objects
  26. Is there a way to avoid automatically updating Rails timestamp fields?
  27. Adding foreign key to a rails model
  28. Rails: Overriding ActiveRecord association method
  29. Is the server running on host “localhost” (::1) and accepting TCP/IP connections on port 5432?
  30. Rails Object Relationships and JSON Rendering

Leave a Comment