PostgreSQL allows using time and number ranges. Searching e.g. overlaps using database operators is really fast.

But sometimes you don't even have to have ranges in the database to utilize them.

User.where(created_at: 55.minutes.ago..Time.current)
User.where(created_at: (Time.current.midnight - 1.day)..Time.current.midnight)

# not the optimal way but works
User.where('created_at BETWEEN :start AND :end',
           start: 1.days.ago,
           end: Time.current)

Most useful ranges are tsrange and daterange but here is the full list. Removed tstzrange as you should never use that with Rails without a really good reason.

-- PostgreSQL definitions:
-- int4range, int8range, numrange, tsrange, daterange

-- includes 3, does not include 7, and does include all points in between
SELECT int4range(3, 7, '[)');

-- does not include either 3 or 7, but includes all points in between
SELECT int4range(3, 7, '()');

-- includes only the single point 4
SELECT int4range(4, 4, '[]');

-- includes no points (and will be normalized to 'empty')
SELECT int4range(4, 4, '[)');

-- if the third argument is omitted, '[)' is assumed.
-- includes 3, does not include 7, and does include all points in between
SELECT int4range(3, 7);

Usage:

class AddPrimeTimeToPubs < ActiveRecord::Migration
  def change
    add_column :pubs, :happy_hour, :tsrange
  end
end

# do the given ranges overlap, even partially?
pub = Pub.find(1)
bar = Pub.find(2)
pub.happy_hour.overlaps?(bar.happy_hour) # => true/false

# search ranges that partially overlap with this range
# "Happy hours that are "
Pub.where("happy_hour && tsrange(?, ?)", 30.minutes.from_now, 1.hours.from_now)

Pub.where("lower(happy_hour) && tsrange(?, ?)", 30.minutes.from_now, 1.hours.from_now)

# same, but with an ActiveRecord
# "Happy hours that are at the same time as my happy hour, even partially."
Pub.where("happy_hour && tsrange(:start, :end) AND id != :id",
           start: pub.happy_hour.first,
           end: pub.happy_hour.last,
           id: pub.id)

# search ranges that contain this range
# "Happy hours that start before my happy hour and end after my happy hour."
Pub.where("happy_hour @> tsrange(:start, :end) AND id != :id",
           start: pub.happy_hour.first,
           end: pub.happy_hour.last,
           id: pub.id)

# search ranges that end before this range
# "Happy hours that end before my happy hour starts."
Pub.where("happy_hour << tsrange(:start, :end)",
           start: pub.happy_hour.first,
           end: pub.happy_hour.last)

# search ranges that start after this range
# "Happy hours that start after my happy hour ends."
Pub.where("happy_hour >> tsrange(:start, :end)",
           start: pub.happy_hour.first,
           end: pub.happy_hour.last)

# "Pubs with happy hour that last at least 30 minutes"
Pub.where("upper(happy_hour) - lower(happy_hour) > :duration",
          duration: '30 minutes')

# "Happy hours that start in 30 minutes and last at least 2 hours."
Pub.where(%q(
            (lower(happy_hour) BETWEEN :start AND :end)
            AND upper(happy_hour) - lower(happy_hour) > :duration
          ),
          start: Time.current,
          end: 30.minutes.from_now,
          duration: '2 hours')

Modeling a weekly schedule. This style allows modeling time ranges e.g. schedule events that take up to 24 hours. Or just use a gem.

Use following dates for your "standard week":
= Start of Monday '1996-01-01 00:00' - Start of Tuesday '1996-01-09 00:00'
= The extra day (9th) is for ranges that start on Sunday but end on Monday.
  No time range should ever start on 9th.

DATE   WD     ISODOW    NOTES
01   = Mon  = 1         =
02   = Tue  = 2         =
03   = Wed  = 3         =
04   = Thu  = 4         =
05   = Fri  = 5         =
06   = Sat  = 6         =
07   = Sun  = 7         =
08   = Mon  = 1         = Time ranges that start on Sunday and end on Monday.

So when saving a weekly even, you must check for the one special case
that range starts on Sunday but end on Monday.
Same when checking overlaps and timestamp searches.

The range column should be type `tsrange`.

Add constraint to the column
CHECK (t_range <@ '[1996-01-01 00:00, 1996-01-09 00:00)')

SELECT EXTRACT(ISODOW FROM lower(t_range)) AS isodow_from, -- day of week lower
       EXTRACT(ISODOW FROM upper(t_range)) AS isodow_to,   -- day of week upper
       lower(t_range)::time AS time_from,                  -- start time
       upper(t_range)::time AS time_to                     -- end time
FROM schedule;

Sources

• Store the day of the week and time?