Rails. How to Store Time of Day (For Schedule)

Store the day of the week and time?

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

Order by time of day and regardless of date

This is a SQLite3 Ruby connector issue. Works fine on Postgres.

Looks like SQLite does not have a native understanding of the Time datatype as stated here: Rails Active Record find(:all, :order => ) issue and here: http://www.sqlite.org/datatype3.html

Renaming the attribute from time to i.e. class_time did not solve the problem as recommended here: Sqlite3 activerecord :order => "time DESC" doesn't sort

In ruby on rails how would I best go about storing ONLY hours and minutes?

Useful information: (source: Is there documentation for the Rails column types?)

• Date : Stores only a date (year, month, day)
• Time : Stores only a time (hours, minutes, seconds)
• DateTime : Stores both date and time
• Timestamp : Stores both date and time

Note: For the purposes of Rails, both Timestamp and DateTime mean the same thing (use either type to store both date and time). For the TL;DR description of why both exist, read the bottom paragraph (in source link).

So yes, you can do something like

start_time:time
end_time:time

in forms you can use an input type time

<input type="time" name="start_time">

i think this is the best way.

How to schedule a worker for a time in an specific Time zone in Rails?

To explicitly get a specific TimeZone instance, use ActiveSupport::TimeZone::[] and call ActiveSupport::TimeZone#parse to create a TimeWithZone instance within that timezone:

time = ActiveSupport::TimeZone['London'].parse('12/08/2015 15:00')
#=> Wed, 12 Aug 2015 15:00:00 BST +01:00

or using ActiveSupport::TimeZone#local:

time = ActiveSupport::TimeZone['London'].local(2015, 8, 12, 15, 0)
#=> Wed, 12 Aug 2015 15:00:00 BST +01:00

Calling TimeWithZone#utc returns a Time instance in UTC:

time.utc
# => 2015-08-12 14:00:00 UTC

ActiveSupport also provides Time::use_zone to set Time.zone inside a block:

Time.use_zone('London') { Time.zone.parse('12/08/2015 15:00') }
#=> Wed, 12 Aug 2015 15:00:00 BST +01:00

Note that ActiveRecord automatically saves DATETIME fields in UTC, so there's usually no need to convert the time to UTC.

---

Related Topics