Storage contract

How Tempo expressions map to PostgreSQL range types, what survives a store-and-load cycle, and — importantly — what does not.

This is a reference document. Every claim here is enforced by Tempo.SQL.Conversion and Tempo.SQL.Meta and covered by the test suite.

Two storage modes

tempo_sql offers two parallel storage strategies. Pick one per column:

On SQLite, which has no range type, Tempo.Ecto.SQLite.TempoRange keeps the whole expression as JSON beside integer bounds; the SQLite guide covers it.

Each has a floating counterpart, for a column declared frame: :floating (see The frame of a column): tsrange / tsmultirange, and tempo_floating_range / tempo_floating_multirange.

Pick plain range when the downstream workflow only cares about the span — start, end, overlap relationships. Queries are native Postgres operators. Minimal schema overhead.

Pick composite when you need the expression back as it was written — qualifications, calendars other than the Gregorian, recurrences, zones, metadata, or the resolution a value was written to. The composite stores a queryable tstzrange and a jsonb meta column holding the expression's ISO 8601 text. Costs: a CREATE TYPE migration, a different column type, composite-aware query macros, and one extra column's worth of storage per row.

Most of the rest of this guide is about the plain-range mode, because the composite mode keeps the whole expression — the one-paragraph summary is "whatever you put in comes back as it was written, and range queries still work via (column).range". Composites have their own section at the end.

The plain-range mapping

tstzrange is a timezone-aware range of two timestamptz values, and tsrange a range of two timestamp values, which have no zone. tstzmultirange and tsmultirange (PostgreSQL 14+) are ordered, disjoint sets of them. Both follow the half-open [lower, upper) convention, which is the convention every Tempo span has underneath — so adjacency, ordering, and emptiness all compose cleanly across the boundary.

An end written to a date is read through it, as it is said aloud: ~o"2026-06-15/2026-06-20" is six days, and holds the 20th. It is stored as the instants it runs between, [2026-06-15 00:00, 2026-06-21 00:00). An end written to the clock is a boundary: ~o"2026-06-15T09/2026-06-15T17" is stored as [09:00, 17:00). Tempo.Interval.instants/1 gives the two instants any interval is stored as.

The frame of a column

A Tempo value lies on one of two lines. A value in a zone ([Europe/Paris]) or at a time shift (Z, +02:00) is on the UTC line: it names instants every clock agrees on. A value with neither is floating: ~o"2026-06-15T09:00" is nine in the morning wherever it is read, as a shop's opening hours are. Tempo never compares or combines values on two lines, and neither does a column.

A column's :frame field option names its line. The default, :utc, is a timestamptz range, and holds a value on the UTC line as the UTC instants it is. :floating is a timestamp range, and holds a floating value as the wall-clock times it is:

schema "shops" do
  field :opening_hours, Tempo.Ecto.Interval, frame: :floating
  field :last_delivery, Tempo.Ecto.Interval
end

A value given to a column of the other frame is refused with the reason :frame: a floating value is not placed at UTC, and a zoned value does not lose its zone to become floating. The refusal comes at cast/2, as a changeset error on the field that names the frame to declare, and again at dump/3 for a value that reaches the repository without a changeset. The migration helpers take the same option, add_interval :opening_hours, frame: :floating.

What a round-trip looks like

A minimal setup, three Tempo values, pipeline through the type module:

iex> import Tempo.Sigils
iex> floating = Tempo.Ecto.Interval.cast_type(frame: :floating)
iex> year = ~o"2026"
iex> meeting = ~o"2026-06-15T09:00/2026-06-15T10:00"
iex> offset = ~o"2026-06-15T09:00+02:00/2026-06-15T10:00+02:00"
iex> {:ok, year_range} = Ecto.Type.dump(floating, year)
iex> {:ok, meeting_range} = Ecto.Type.dump(floating, meeting)
iex> {:ok, offset_range} = Tempo.Ecto.Interval.dump(offset)
iex> Ecto.Type.load(floating, year_range)
{:ok, ~o"2026-01-01T00:00:00/2027-01-01T00:00:00"}
iex> Ecto.Type.load(floating, meeting_range)
{:ok, ~o"2026-06-15T09:00:00/2026-06-15T10:00:00"}
iex> Tempo.Ecto.Interval.load(offset_range)
{:ok, ~o"2026-06-15T07:00:00Z/2026-06-15T08:00:00Z"}
iex> Tempo.Ecto.Interval.dump(meeting)
:error

The year is stored as the times that bound it, and loads back as an interval between them — the "it was just a year" fact is gone. The floating meeting comes back at the same times on the clock. The meeting at +02:00 comes back as the same instants at UTC, two hours earlier on the clock. A column on the UTC line refuses the floating meeting.

That is the whole guide in one paragraph. The rest of this document makes each claim precise.

What is retained

For every storable expression, the following survive a round-trip exactly:

• The instants each span begins and ends at, to the second.

• The half-open [from, to) convention. Adjacent spans remain adjacent, touching spans still touch.

• Open ends. ~o"2026-06-15T09:00/.." is stored as a range with upper: :unbound (SQL [lower, )), and loads back with an open end. An open-ended booking like "everything after 9 on the 15th of June" round-trips as it went in.

• For tstzmultirange, the order of the spans. Postgres keeps a multirange's overlapping or adjacent ranges as one, so spans that touch come back joined: a recurrence's back-to-back occurrences, ~o"R3/2026-06-15T09:00/PT15M", come back as a set of one span, ~o"{2026-06-15T09:00:00Z/2026-06-15T09:45:00Z}".

What is lost

The following are dropped on store. They cannot be recovered on load.

1. The resolution a value was written to

This is the most important loss and has its own section below. ~o"2026" is stored as the range [2026-01-01 00:00:00Z, 2027-01-01 00:00:00Z) and loads back as an interval between those two instants. That it was written as a year is not recoverable from the range, unless the column declares :resolution.

2. The zone

An expression in a named zone, or at a time shift, is stored as the UTC instants it is: PostgreSQL's tstzrange keeps every value at UTC, and the zone's name is not stored. On load the expression is at UTC (Z) — the instants are kept, the "it was in America/New_York" fact is not. A floating expression, in a floating column, has no zone to lose: it is stored and loaded as the times on the clock it is.

3. Metadata

The map Tempo.put_metadata/2 sets, on an expression or on the members of a set, has no place in a range. It is dropped on store and loaded expressions come back with no metadata.

4. What the expression was

A value, an interval, a set and a recurrence are different expressions with spans. Postgres ranges hold only the spans. ~o"2026", ~o"2026/2026" and ~o"2026-01-01T00:00/2027-01-01T00:00" all store as the same range, and all load back as the last.

What is rejected

The storage contract distinguishes between dropped (lost on store) and rejected (the dump/1 callback returns :error, Ecto raises Ecto.ChangeError on insert). Rejection is the library's way of saying "this expression has no faithful representation in a tstzrange — the caller must make a decision". Each refusal is a Tempo.SQL.UnsupportedValueError from Tempo.SQL.Conversion, carrying its reason:

• Several spans in one range (:multi_valued_slot). Tempo.Ecto.Interval holds one span. A set, a recurrence, or a value that names several (~o"2026Y{6,8}M", June and August) belongs in a Tempo.Ecto.IntervalSet column, one range a span.

• A recurrence with no end. ~o"R/2026-06-01/P1W" has no last span, so no multirange holds it. Bound it to a window with Tempo.to_interval/2 and :within, or store it in a composite column.

• Qualifications (:qualification). ~o"1984?/2004" has no Postgres-range analogue — ranges are precise, uncertainty is not. Callers who need to persist uncertainty should store the expression in a composite column, or the qualification in a sibling column.

• A value of the other frame (:frame). A column on the UTC line refuses a floating value, which has no instant until it is given a zone, and a floating column refuses a value in a zone or at a time shift. Declare the column with the frame its values have, or give the value a zone with Tempo.in_zone/2.

• Calendars other than the Gregorian (:non_gregorian_calendar). Values of the Hebrew, Persian or another calendar are rejected. The library does not convert for you; convert with Tempo.to_calendar/2 before storing.

• A value with no year (:partial_resolution). ~o"T09/T17" is the hours from nine to five of no day in particular, and names no instant. Place it in a day first.

• Fully-unbounded intervals (:fully_unbounded). ~o"../.." would serialise to the Postgres range (,) — a range that contains every instant. This is almost always a caller error (unset fields), so we reject rather than store silently. Callers who genuinely want the universal range can store NULL in a nullable column.

• An empty set. An empty set serialises to '{}'::tstzmultirange, which is valid Postgres but usually indicates a caller error. Callers who want "no set" should use a NULL column.

What looks refusable but is not: an ordinal date (~o"2026-181", the 30th of June), a week date (~o"2026-W23-4"), and a whole week (~o"2026-W23", the seven days from Monday the 1st of June 2026) each name days, and store as the days they name.

Resolution and round-trip

The resolution a value is written to is what it means:

• ~o"2026" is the year.

• ~o"2026-06" is the month.

• ~o"2026-06-15" is the day.

• ~o"2026-06-15T09" is the hour.

• ~o"2026-06-15T09:30:00" is the second.

Each of these is a valid Tempo value and each denotes a different span. ~o"2026" is the whole of 2026; ~o"2026-06-15T09:30:00" is one second.

PostgreSQL ranges cannot express this distinction. Every tstzrange is two timestamps — the resolution of the source value does not exist in the type system. '[2026-01-01 00:00:00+00, 2027-01-01 00:00:00+00)'::tstzrange is the storage representation of:

• ~o"2026" — the year

• ~o"2026/2026" — an interval from the year through the year

• ~o"2026-01-01T00:00/2027-01-01T00:00" — a one-year booking written to the minute

All three store as the exact same range. On load we return the third form — an interval between two instants — because that is the only shape the loaded range guarantees.

This means: if the caller stores ~o"2026" and loads it back, they do not get ~o"2026". They get ~o"2026-01-01T00:00:00Z/2027-01-01T00:00:00Z". It covers the same instants and gives the same answers to Tempo.contains?/2, Tempo.overlaps?/2 and Tempo.within?/2. But the expression is different, and code that asks for its resolution gets the second.

Preserving resolution — the options

Option 1 — the :resolution field option. Declare the resolution the column holds, and loaded values come back written to it:

schema "reports" do
  field :reporting_year,    Tempo.Ecto.Interval, resolution: :year
  field :reporting_quarter, Tempo.Ecto.Interval, resolution: :month
  field :daily_window,      Tempo.Ecto.Interval, resolution: :day
  field :meeting_window,    Tempo.Ecto.Interval   # defaults to :second
end

The option takes a unit: :year, :month, :day, :hour, :minute, or :second. On load, both ends are written to that unit. An end at a calendar unit is read through it, so the range's upper bound becomes the last year, month or day it holds:

"Load the range as the years it holds — 2026 through 2026." "Load the quarter as the months it holds — April through June."

This is an assertion by the caller about what the column holds, not a heuristic. The loader writes the ends to the unit unconditionally — it does not peek at the bytes and guess. A column declared resolution: :year always loads as years, regardless of what was actually stored.

Caveats:

• :resolution only affects load/3, not dump/3. An expression is always stored as the instants it runs between.

• A column with mixed-resolution data is a footgun. If some rows were written as ~o"2026" and others as ~o"2026-06-15T09:30:00Z/2026-06-15T10:00:00Z", declaring any single :resolution writes both to it — genuine instants come back as whole units. The option is for columns that hold data of one resolution, which is the common schema-level case but not universal.

• :resolution does not change the stored bytes. Switching the option later is a safe schema change — no data migration needed.

• A range shorter than the unit is refused. With resolution: :day, a stored range of [2026-06-15 09:00:00Z, 2026-06-15 10:00:00Z) begins on the 15th and ends through the 14th, which is no interval, so loading it is an error rather than a span that covers nothing. The option assumes your writers respect the declared resolution; it cannot undo bad data.

Option 2 — a composite column. For columns that hold data of mixed resolutions, or need the whole expression back, use Tempo.Ecto.TempoRange or Tempo.Ecto.TempoMultirange: the range column carries the queryable span and the meta column the expression's ISO 8601 text. See the composite section below.

Bracket conventions

A Tempo span is half-open [from, to) underneath — inclusive first, exclusive last. PostgreSQL ranges support all four bracket shapes: [], [), (], (). Unlike discrete range types (int4range, daterange) which Postgres canonicalises to [) on output, tstzrange and tstzmultirange preserve the bracket shape you wrote. A column populated by another writer can therefore hand you a [a, b] or (a, b) range.

tempo_sql normalises anything non-half-open to [) on load by shifting the offending endpoint one second:

For a range of whole seconds, which is what tempo_sql writes for an expression of whole seconds, the one-second shift is exact — the loaded interval covers the same instants as the stored range. So tempo_sql columns are safe to share with writers that use any bracket convention at whole seconds.

On the dump side, tempo_sql always emits [lower_inclusive: true, upper_inclusive: false], matching Tempo's convention.

Composite mode — tempo_range and tempo_multirange

The composite types keep the whole expression. Use them when the plain-range mode's losses would hurt — recurrences, qualifications (:uncertain, :approximate), calendars other than the Gregorian, zones, the resolution a value was written to, metadata.

Setup

One-time migration for the Postgres composite types:

defmodule MyApp.Repo.Migrations.CreateTempoTypes do
  use Ecto.Migration
  import Tempo.SQL.Migration

  def up do
    create_tempo_types()
    create_tempo_types(frame: :floating)
  end

  def down do
    drop_tempo_types(frame: :floating)
    drop_tempo_types()
  end
end

The call with frame: :floating is needed only for floating composite columns. The first call creates:

CREATE TYPE tempo_range AS (
  range      tstzrange,
  resolution text,
  meta       jsonb
);

CREATE TYPE tempo_multirange AS (
  ranges     tstzmultirange,
  resolution text,
  meta       jsonb
);

The floating call creates tempo_floating_range and tempo_floating_multirange, the same with a tsrange and a tsmultirange.

The three fields: range/ranges holds the queryable span; resolution records the column's declared resolution (for documentation); meta is a JSON document holding the expression's ISO 8601 text, its metadata, and each member's metadata for a set.

Postgrex encodes the jsonb field with the JSON library its types name, Jason unless told otherwise. tempo_sql ships Tempo.SQL.PostgresTypes, which names Tempo.SQL.JSON, a shim over Erlang's built-in :json; name it in the repository's configuration:

config :my_app, MyApp.Repo, types: Tempo.SQL.PostgresTypes

An application that needs other Postgrex extensions defines its own types module with the same JSON library: Postgrex.Types.define(MyApp.PostgresTypes, [MyApp.SomeExtension], json: Tempo.SQL.JSON).

Schema

schema "meetings" do
  field :window, Tempo.Ecto.TempoRange
end

schema "calendars" do
  field :busy_times, Tempo.Ecto.TempoMultirange
end

schema "shops" do
  field :opening_hours, Tempo.Ecto.TempoRange, frame: :floating
end

The Ecto API is the same as the plain-range types': cast, dump and load take and return Tempo.t/0 expressions. A composite takes any expression that has ISO 8601 text and a place on the time line, and keeps it as it was given.

What round-trips

A composite column keeps the expression's ISO 8601 text, which Tempo reads back as it was written:

• Resolution. A stored ~o"2026" loads as ~o"2026", not as an interval between two instants. This is the headline difference from the plain-range mode.

• Qualifications. ~o"1984?/2004~" loads as it was stored.

• Recurrences. ~o"R5/2022-01-01/P1M" loads back as the same recurrence, and ~o"R/2022-01-01/P1M", which has no end, is stored with a range from its first occurrence on, unbounded above.

• Calendars. ~o"5786-01-01/5786-01-10[u-ca=hebrew]" loads back in the Hebrew calendar; its range is the Gregorian instants it covers.

• Zones. IANA names ([America/New_York]) survive, not just UTC offsets.

• Sets. A set loads back with its members as they were, each with its own metadata.

• Metadata, on the expression and on each member of a set, provided the map is JSON-serialisable (strings, numbers, booleans, nested maps/lists). It comes back with string keys.

The range a composite stores is the stretch the expression covers: from the start of its first span to the end of its last, so a set's range includes the gaps between its members, and range queries find the expression wherever any of it lies.

Queries

The standard Postgres range operators (@>, &&, -|-) don't apply directly to composite columns — they must reach into the range field. Use the parallel query API:

import Tempo.Ecto.QueryAPI.Composite

from m in FidelityMeeting,
  where: overlaps(m.window, ^search_range)

The macros expand to fragment("(?).range && ?", m.window, search_range) — same operator names and Allen-algebra semantics as Tempo.Ecto.QueryAPI, just auto-unwrapping.

Mixing Tempo.Ecto.QueryAPI (plain-range macros) with a composite column produces a SQL error. Mixing Tempo.Ecto.QueryAPI.Composite with a plain tstzrange column also fails. Choose one import per query module; a query module that mixes columns should qualify both imports.

What the composite still cannot do

• Fully-unbounded intervals (~o"../..") are still rejected. The range field needs a bound on at least one side for any Postgres range-operator query to be meaningful. Use a NULL column if you need "no interval".

• Expressions with no place on the time line: a value with no year (~o"T09/T17"), a duration on its own, and a recurrence counted back with no start (~o"R/P1W/2026-06-01").

• An empty set is still rejected. Use NULL.

• Metadata that contains non-JSON-serialisable terms (tuples, pids, structs) will raise on dump. Convert them at the application layer before storing.

Version 1 and 2 rows

tempo_sql 0.2.0 and earlier, on Tempo 1.x, wrote the meta column as version 1 or 2: each end of an interval on its own. Tempo 1.x read an interval's end as the instant it stops before, where Tempo 2.0 reads an end written to a date through it, so those rows are read as they were written to mean: {"from": "2022Y1M1D", "to": "2022Y1M5D"} is ~o"2022-01-01/2022-01-04", the same four days. A recurrence is rebuilt from its start, its cadence and its count. No migration is needed; a row is written as version 3 when it is next stored.

Trade-offs

Composite types are not a free upgrade:

• Storage. Every row carries a jsonb blob in addition to the range column. For homogeneous-shape data where you don't need fidelity, the plain-range mode is cheaper.

• Indexes. GiST indexes apply to the range field, not the whole composite. Index the sub-field explicitly: CREATE INDEX ON meetings USING gist (((window).range)). The test suite skips this for simplicity; production workloads should add it.

• Third-party tooling. A plain tstzrange column is understood by every Postgres client, ORM, and BI tool. A tempo_range composite is not — downstream systems need to either know about the type or unwrap it via (column).range in a view.

The guidance is: plain-range for the common case, composite when the schema has load-bearing Tempo shape that matters.

Summary

If all the caller cares about is the span — its start, end, and overlap relationships — the plain range round-trips faithfully. If the column holds data of one resolution, declare :resolution on the field. If the column holds mixed resolutions, or the expression matters, use a composite column.