skip to content

In a Rails 8.1 app on PostgreSQL, what Ruby value does a daterange column hold for a sneaker drop window, and how do you query it?

level: middleimportance: nice to knowfreq 15%

answer

  1. t.daterange maps to a Ruby Range
  2. read back with an exclusive end
  3. unbounded end becomes an endless range
  4. where(column: range) is equality
  5. @> containment in a SQL fragment

basics

~20 s

A daterange column holds a Ruby Range of Dates, read back with an exclusive end, so an inclusive 1..7 window returns as 1...8. where(drop_window: range) compares whole ranges; to find drops open today, use where("drop_window @> ?::date", Date.current).

solid answer

~40 s

`t.daterange :drop_window` stores a PostgreSQL range and Active Record maps it to a Ruby `Range` of `Date`s. PostgreSQL stores date ranges in a canonical half-open form, so assigning `Date.new(2026, 11, 1)..Date.new(2026, 11, 7)` reads back after reload as `Date.new(2026, 11, 1)...Date.new(2026, 11, 8)`; compare with `cover?`, not by inspecting `end`. An unbounded upper end comes back as an endless range (`date..nil`), and a numeric range uses `Float::INFINITY`. For queries, a Range value in a hash condition is equality on a range column (`where(drop_window: r)` matches only identical ranges), so membership is a fragment: `Sneaker.where("drop_window @> ?::date", Date.current)`. Overlap bans between drops are an exclusion constraint with `using: :gist`.

go deeper

for a junior

Recall that t.daterange gives a Ruby Range of Dates and that containment queries use a SQL fragment.

for a middle

Explain canonical exclusive ends, endless ranges for open dates, and why a Range in where means equality on a range column.

for a senior

Use GiST indexes and an exclusion constraint for overlap rules, and handle ExclusionViolation in the model.

for a principal

Decide between range columns and paired columns based on which questions the business asks of its time windows.

## Declaring a range column A sneaker marketplace runs **drops**: a release is purchasable only within a window. PostgreSQL range types store such a window in one column, and the PostgreSQL adapter adds a column method for each built-in range: | Column method | PostgreSQL type | Ruby elements | |---|---|---| | `t.daterange` | `daterange` | `Date` | | `t.tstzrange` | `tstzrange` | `Time` with zone | | `t.tsrange` | `tsrange` | `Time` | | `t.int4range`, `t.int8range` | integer ranges | `Integer` | | `t.numrange` | `numrange` | `BigDecimal` | ```ruby create_table :drops do |t| t.references :sneaker, null: false, foreign_key: true t.daterange :drop_window, null: false t.timestamps end ``` ## What Active Record hands back The adapter's range type turns PostgreSQL's text form into a Ruby `Range`: - **Exclusive ends after a round trip.** PostgreSQL stores discrete ranges such as `daterange` in a canonical form that includes the lower bound and excludes the upper one. Assign `Date.new(2026, 11, 1)..Date.new(2026, 11, 7)`, reload, and you get `Date.new(2026, 11, 1)...Date.new(2026, 11, 8)`. Both cover the same days, so `drop_window.cover?(Date.new(2026, 11, 7))` stays true, but code that prints `drop_window.end` as the last day is now off by one. - **Unbounded ends.** An upper bound of `infinity` or an empty bound is read as infinite. For a date range Rails turns that into an **endless Ruby range** (`Date.new(2026, 11, 1)..nil`); for numeric ranges it keeps `Float::INFINITY`. - **No exclusive starts.** A Ruby `Range` cannot exclude its beginning, so a finite lower bound written as `(` in PostgreSQL, possible with `tsrange` or `numrange`, makes Rails raise `ArgumentError` on read. - **Empty ranges** come back as `nil`. `db/schema.rb` writes range defaults with `..` or `...` to keep the inclusivity visible. ## Querying The range type forces equality for a Ruby `Range` value, exactly as array columns do for Arrays: | Code | Meaning | |---|---| | `Drop.where(drop_window: Date.current..Date.current + 6)` | windows **equal** to that range | | `Drop.where("drop_window @> ?::date", Date.current)` | windows that **contain** today | | `Drop.where("drop_window && daterange(?, ?)", from, to)` | windows that **overlap** a period | The first row is the trap. On an ordinary `date` column, `where(released_on: a..b)` builds `BETWEEN`; on a range column the same syntax compares whole ranges. Containment and overlap go in fragments, with the dates bound. The full operator set belongs to PostgreSQL's own documentation. For speed, a GiST index (`add_index :drops, :drop_window, using: :gist`) serves containment and overlap. ## Preventing overlapping drops A business rule like "one sneaker cannot have two overlapping drops" belongs in the database: ```ruby enable_extension "btree_gist" add_exclusion_constraint :drops, "sneaker_id WITH =, drop_window WITH &&", using: :gist, name: "drops_no_overlap" ``` The `btree_gist` extension lets the GiST index compare the plain `sneaker_id` column with `=`. A violating insert raises `ActiveRecord::ExclusionViolation`, which a model can rescue to add a form error. Two concurrent inserts cannot both pass, which a Ruby validation alone cannot promise. ## Assigning ranges from forms and code - Assign a Ruby `Range` (`start_on..end_on`) from two form fields; the type casts each bound through the date subtype. - A string in PostgreSQL's own text form, such as `"[2026-11-01,2026-11-08)"`, is parsed as well, which helps when importing CSV files. - For `tstzrange`, the bounds are times with zones, so build them with `Time.zone` to avoid drops opening at midnight UTC instead of local time. ## When two columns are simpler 1. Most queries ask only "has it started" or "has it ended" and never compare windows with each other. 2. The data must move to a database without range types. 3. The team finds `starts_on` and `ends_on` clearer than exclusive-end arithmetic. Ranges earn their place when overlap and containment are the questions you ask most.

  • Why does a daterange saved as Date.new(2026, 11, 1)..Date.new(2026, 11, 7) reload with an end of November 8?
    PostgreSQL stores discrete ranges like `daterange` in canonical half-open form, `[2026-11-01,2026-11-08)`. Rails maps the closing parenthesis to an exclusive Ruby range, `...Date.new(2026, 11, 8)`. The days covered are identical, so use `cover?` and treat `end` as exclusive.
  • How do you stop two drops for the same sneaker from overlapping in a Rails app on PostgreSQL?
    Add an exclusion constraint: enable `btree_gist`, then `add_exclusion_constraint :drops, "sneaker_id WITH =, drop_window WITH &&", using: :gist`. PostgreSQL rejects overlapping rows even under concurrent inserts, and Rails raises `ActiveRecord::ExclusionViolation`, which you can turn into a validation message.

A daterange reads like a hotel booking: check in on November 1, check out on November 8, and you stayed the nights of the 1st through the 7th. PostgreSQL stores the check-out day as the excluded end, which is why Rails hands back 1...8.

  • Expects where(drop_window: range) to find windows containing a date
  • Reads drop_window.end as the last day after a daterange round trip
  • Expects Float::INFINITY for an open-ended daterange
  • Enforces non-overlapping drops with a Ruby validation only
  • Thinks Rails stores a range column as two hidden columns