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?
answer
- t.daterange maps to a Ruby Range
- read back with an exclusive end
- unbounded end becomes an endless range
- where(column: range) is equality
- @> containment in a SQL fragment
basics
~20 sA 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
Recall that t.daterange gives a Ruby Range of Dates and that containment queries use a SQL fragment.
Explain canonical exclusive ends, endless ranges for open dates, and why a Range in where means equality on a range column.
Use GiST indexes and an exclusion constraint for overlap rules, and handle ExclusionViolation in the model.
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