skip to content

In PostgreSQL, why store a shop's opening hours as tstzrange or tstzmultirange rather than opens_at and closes_at columns, and where does a single range run out?

level: seniorimportance: should knowfreq 38%

answer

  1. bounds live inside the value
  2. the default bracket pair
  3. continuous types are not canonicalised
  4. a gap cannot fit in one range
  5. range_agg and unnest

basics

~20 s

A tstzrange is one value that records its own bound inclusivity, so containment, overlap and adjacency become single operators (@>, &&, -|-) that handle edges and open ends. One range cannot hold a gap; a lunch break needs a tstzmultirange.

solid answer

~40 s

With two columns every query re-derives the predicate (`opens_at <= t AND t < closes_at`), the inclusive/exclusive choice drifts between queries, and a NULL `closes_at` meaning "open-ended" breaks plain comparisons. A `tstzrange` carries its bounds in the value: the two-argument constructor builds `[)`, an omitted bound means unbounded, and `'empty'` exists. Then `hours @> now()`, `a && b` and `a -|- b` replace hand-written logic, and the type is what GiST indexes and exclusion constraints build on. `tstzrange` is continuous, so `[9:00,17:00]` and `[9:00,17:00)` stay different values, while `daterange` is canonicalised to `[)`. A single range runs out at gaps: subtracting a lunch break raises `result of range difference would not be contiguous`. A `tstzmultirange` holds ordered, non-overlapping ranges, its `-` and `+` never fail, and `range_agg` builds one from rows.

code

sql · 17 lines
sql
CREATE TABLE shop_hours (
  shop_id     bigint NOT NULL,
  open_during tstzmultirange NOT NULL
);

-- 09:00-17:00 minus a 12:00-13:00 lunch break: two ranges, no error
INSERT INTO shop_hours
VALUES (7,
  tstzmultirange(tstzrange('2026-10-05 09:00+02', '2026-10-05 17:00+02'))
  - tstzmultirange(tstzrange('2026-10-05 12:00+02', '2026-10-05 13:00+02')));

SELECT shop_id FROM shop_hours WHERE open_during @> now();

-- the same subtraction on plain ranges fails
SELECT tstzrange('2026-10-05 09:00+02', '2026-10-05 17:00+02')
     - tstzrange('2026-10-05 12:00+02', '2026-10-05 13:00+02');
-- ERROR:  result of range difference would not be contiguous

go deeper

for a junior

Recall that a range stores both ends plus whether each is included, and that the default constructor gives [) bounds.

for a middle

Explain @>, && and -|-, the discrete-versus-continuous difference, and why daterange's upper() is the day after the last included day.

for a senior

Argue from production bugs: hand-written interval predicates drift, NULL open ends vanish from results, and gaps need multiranges because range difference errors.

for a principal

Decide where interval logic lives: in a range type with constraints the database enforces, or scattered across every query that reads two columns.

## What two columns make every query re-derive A recipe site lists partner shops and when each is open on a given day. With `opens_at timestamptz` and `closes_at timestamptz`, every query must restate the interval logic by hand: - "Open now" becomes `opens_at <= now() AND now() < closes_at`, and one query will use `<=` on both ends. - "Do two slots overlap" becomes `a.opens_at < b.closes_at AND b.opens_at < a.closes_at`, which is easy to get subtly wrong at the touching edge. - "Open until further notice" is usually a NULL `closes_at`, and every comparison with NULL is NULL, so those shops disappear from results. A **range type** stores the interval as one value that also records whether each bound is included. The logic moves out of every query and into the type's operators. ## Bounds, empty and unbounded ranges | Literal | Meaning | |---|---| | `[a,b)` | includes `a`, excludes `b`; the constructor default | | `[a,b]` | includes both ends | | `(a,b)` | excludes both ends | | `[a,)` | from `a` with no upper bound | | `empty` | contains no points | `tstzrange(a, b)` with two arguments builds `[a,b)`; a third argument such as `'[]'` chooses other bounds, and passing `NULL` for a bound makes that side unbounded. Equal bounds that are not both inclusive produce an empty range, and a lower bound above the upper raises `range lower bound must be less than or equal to range upper bound`. The functions `lower`, `upper`, `lower_inc`, `upper_inc`, `lower_inf`, `upper_inf` and `isempty` read the parts back. An omitted bound is not the same as the special timestamp value `infinity`: `[today,infinity)` excludes `infinity`, while `[today,)` is unbounded and lies beyond it. ## Discrete versus continuous: daterange canonicalises, tstzrange does not The built-in **discrete** range types `int4range`, `int8range` and `daterange` have a canonical form, `[)`. A closed date range is rewritten on input, so `daterange('2026-12-24', '2026-12-26', '[]')` is stored as `[2026-12-24,2026-12-27)` and `upper()` returns the 27th, the day **after** the last included day. Code that shows `upper()` as "last day" is off by one. `tstzrange`, `tsrange` and `numrange` are **continuous**: there is no next value, so no canonicalisation. `[09:00,17:00]` and `[09:00,17:00)` are different values and compare unequal. Pick `[)` everywhere so consecutive slots meet without overlapping. ## Operators that replace hand-written predicates | Operator | Question | Example | |---|---|---| | `@>` | does the range contain this instant or range | `hours @> now()` | | `&&` | do the ranges share any point | `a && b` | | `-\|-` | are they adjacent, touching without overlap | `[9,12)` and `[12,17)` | | `*` | intersection | common opening time of two shops | | `+`, `-` | union, difference | fail if the result would have a gap | Adjacency matters at the edge: `[09:00,12:00)` and `[12:00,17:00)` do **not** overlap, because no instant belongs to both, but `-|-` says they touch. ## Where a single range runs out: multiranges A shop open 09:00 to 17:00 with a lunch break from 12:00 to 13:00 is two intervals. Range union and difference raise an error when the result would need two disjoint pieces: `result of range difference would not be contiguous`. Every range type has a **multirange** partner, here `tstzmultirange`: an ordered list of non-overlapping, non-empty ranges, with literals such as `{[a,b), [c,d)}`. 1. Multirange `+`, `-` and `*` never fail on gaps; cast or wrap ranges with `tstzmultirange(...)` first. 2. Most range operators also accept multiranges, so `open_during @> now()` still works. 3. `range_agg(slot)` aggregates rows of ranges into one multirange, merging overlapping and adjacent slots; `range_intersect_agg` returns the common part. 4. `unnest(multirange)` turns it back into rows of ranges in ascending order. ## What ranges do not give you on their own - **Speed.** Range predicates want a GiST or SP-GiST index; that choice belongs with GiST indexing. - **No-overlap guarantees.** An `EXCLUDE USING gist` constraint, or a PostgreSQL 18 `WITHOUT OVERLAPS` key, enforces them; both are constraint features built on this type. - **Recurring rules.** `tstzrange` holds absolute instants, so "Fridays 22:00 to 02:00" is a rule, not a range. A custom range over `time` cannot express it either, because the lower bound would exceed the upper. Store the rule and materialise upcoming slots as `tstzrange` values.

  • What does upper() return for daterange('2026-12-24', '2026-12-26', '[]'), and why?
    It returns 2026-12-27. `daterange` is discrete, so PostgreSQL canonicalises it to `[2026-12-24,2026-12-27)`, and `upper()` returns that stored exclusive bound, the day after the last included day; `upper_inc()` is false. Displaying `upper()` as the closing day is an off-by-one bug.
  • How do you turn many per-slot rows into one shop's schedule, and back?
    `range_agg(slot)` grouped by shop aggregates `tstzrange` rows into a `tstzmultirange`, merging slots that overlap or touch. `unnest(open_during)` expands a multirange back into rows of ranges in ascending order, and `range_intersect_agg` gives the time when every shop in a group is open.
  • Why can't a range model a weekly rule such as Friday 22:00 to Saturday 02:00?
    A `tstzrange` holds absolute instants, so it describes one Friday night, not every one. A user-defined range over `time` fails too: 22:00 to 02:00 has a lower bound above the upper, which raises an error. Store the rule as day-of-week plus times and materialise upcoming slots as `tstzrange` values.

  • A tstzrange's upper bound is inclusive unless you say otherwise.
  • PostgreSQL normalises tstzrange bounds, so [09:00,17:00] equals [09:00,17:00).
  • Subtracting a lunch-break tstzrange from the day's tstzrange returns the two halves.
  • A NULL closes_at for 'still open' works fine with ordinary comparisons.
  • The && operator treats touching ranges like [9,12) and [12,17) as overlapping.