Tempo SQL

Ecto types and migration helpers that persist Tempo intervals and interval sets as PostgreSQL tstzrange / tstzmultirange values.

Tempo models time as intervals, not instants. PostgreSQL models the same shape natively — a tstzrangeis a bounded span with the half-open [lower, upper) convention, and tstzmultirange (PG 14+) is a set of disjoint spans. tempo_sql is the adapter between them: a Tempo interval dumps straight to a Postgrex range, loads straight back, and participates in Postgres range queries (@>, &&, -|-) as first-class values.

Installation

Add ex_tempo_sql to your deps:

def deps do
  [
    {:ex_tempo, "~> 1.1"},
    {:ex_tempo_sql, "~> 0.1"},
    {:ecto_sql, "~> 3.13"},
    {:postgrex, "~> 0.19"}
  ]
end

Requires PostgreSQL 14 or later for tstzmultirange. tstzrange alone works on PostgreSQL 9.2+.

Requires Erlang/OTP 27 or later, inherited from ex_tempo — OTP 26 miscompiles Tempo's ISO 8601 tokenizer, so it refuses to build there. Elixir 1.17 and later are supported.

Quick start

1. Migration

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

  def change do
    create table(:meetings) do
      add :name, :string
      add_interval :window, null: false
    end

    create_interval_index :meetings, :window
  end
end

2. Schema

defmodule MyApp.Meeting do
  use Ecto.Schema

  schema "meetings" do
    field :name, :string
    field :window, Tempo.Ecto.Interval
  end
end

3. Insert and query

window = Tempo.Interval.new!(
  from: Tempo.from_iso8601!("2026-06-15T09:00:00"),
  to:   Tempo.from_iso8601!("2026-06-15T10:00:00")
)

%MyApp.Meeting{name: "Standup", window: window} |> Repo.insert!()

import Ecto.Query
import Tempo.Ecto.QueryAPI

search = Tempo.Interval.new!(
  from: Tempo.from_iso8601!("2026-06-15T09:30:00"),
  to:   Tempo.from_iso8601!("2026-06-15T09:45:00")
)

{:ok, search_range} = Tempo.Ecto.Interval.dump(search)

Repo.all(
  from m in MyApp.Meeting,
    where: overlaps(m.window, ^search_range),
    select: m.name
)
#=> ["Standup"]

Meetings whose window overlaps the search range — the pipeline reads like the English sentence.

Ecto types

Plain range — span only, lossy round-trip:

Composite — full Tempo shape, byte-exact round-trip:

Use the composite types when the plain-range mode's losses hurt — recurrence rules, qualifications (:uncertain, :approximate), non-Gregorian calendars, zone identifiers, or the implicit-vs-explicit-span distinction. See the storage contract guide for setup and trade-offs.

Tempo.Ecto.Tempo materialises a partial Tempo value (~o"2026Y", ~o"2026Y-06M") to its explicit span before writing. On load you get back a %Tempo.Interval{} — the "it was just a year token" fact doesn't round-trip.

The :resolution field option

All three types accept a :resolution field option that truncates loaded endpoints to a named Tempo component — :year, :month, :day, :hour, :minute, or :second (the default):

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

A column declared resolution: :year always loads as year-resolution Tempos, regardless of what the underlying tstzrange stored — it's a caller-side assertion about column contents, not a heuristic. See the storage contract guide for the caveats.

Query API

Tempo.Ecto.QueryAPI gives you Allen-named fragments over Postgres range operators:

Migration helpers

Tempo.SQL.Migration exposes three macros:

add_interval :window                # → add :window, :tstzrange
add_interval_set :busy_times        # → add :busy_times, :tstzmultirange
create_interval_index :meetings, :window  # GiST index — required for range-operator speed

All three are thin delegates — you can also write the raw add :window, :tstzrange form.

Storage contract

Not every Tempo value can be stored as a Postgres range. Tempo expresses things that tstzrange / tstzmultirange cannot: qualifications, recurrence rules, non-Gregorian calendars, multi-valued token slots. tempo_sql is explicit about this — rather than silently lose information, it returns :error from dump/1, which surfaces as an Ecto.ChangeError at insert time.

For the full mapping — what is retained, what is dropped, what is rejected, and how Tempo's resolution-by-omission convention interacts with Postgres ranges — see the Storage contract guide. The summary:

What is storable

• Fully-anchored Tempo.Interval values — from and to are %Tempo{} values whose token lists contain enough of year / month / day / hour / minute / second to be materialised to a NaiveDateTime. Sub-fields default to calendar zero (month 1, day 1, hour/minute/second 0), so ~o"2026Y" used as an endpoint lands on 2026-01-01T00:00:00Z.

• Partial open-ended intervals — from: :undefined or to: :undefined (but not both) map to unbounded range sides ((, upper] or [lower, )).

• Tempo.IntervalSet values with at least one member, where every member is itself storable under the rules above. The set's own :metadata field is dropped.

• UTC or offset-shifted Tempo values — a :shift offset is applied to land on UTC before handing the value to Postgres.

What is not storable (returns :error)

• Intervals with recurrence != 1 or a repeat_rule — materialise them first via Tempo.to_interval/1 into a Tempo.IntervalSet and store that instead.

• Intervals that are unbounded on both sides.

• Tempo endpoints with a :qualification (:uncertain, :approximate) — Postgres ranges have no notion of uncertainty.

• Tempo endpoints on a non-Gregorian calendar — no automatic conversion; convert first.

• Tempo endpoints with multi-valued token slots (day_of_week: [1, 3, 5], day: 1..15) — these don't collapse to a single instant.

• Tempo endpoints that still carry a week token (year: 2026, week: 10) or a day with no month (year: 2026, day: 75) — these need calendar conversion to name an instant, so materialise them first via Tempo.to_date/1 / Tempo.to_interval/1. Note that parsing already resolves most of these: ~o"2026-075" and ~o"2026-W10-3" both arrive as ordinary calendar dates and store without complaint. The refusal bites for values built from these tokens directly, and for a bare week such as ~o"2026-W10", which names no single day.

• Empty Tempo.IntervalSet values — use a NULL column if you need "no set".

What is lossy on round-trip (round 1)

For the initial release, a stored value and a loaded value match only on their interval shape, not their metadata. Round-trip discards:

• Tempo.Interval.metadata

• Tempo.IntervalSet.metadata

• Tempo :extended metadata (zone_id, IXDTF tags)

• The distinction between an implicit span (~o"2026Y") and its materialisation (2026-01-01..2027-01-01)

A loaded value is always Gregorian, UTC (shift [hour: 0], zone_id "Etc/UTC"). A future release will add a :text storage variant (ISO 8601 in a text column) that preserves the original Tempo shape byte-for-byte for callers who need perfect round-trip.

Testing against a live database

mix test runs the unit tests in-process (no DB). To exercise the integration tests in test/db_test.exs, configure config/test.exs to point at a PostgreSQL 14+ instance:

config :ex_tempo_sql, Tempo.SQL.Repo,
  adapter: Ecto.Adapters.Postgres,
  database: "tempo_sql_test",
  username: "...",
  hostname: "localhost",
  pool: Ecto.Adapters.SQL.Sandbox,
  priv: "test/support"

mix test is aliased to run ecto.drop / ecto.create / ecto.migrate before the suite.

License

Apache-2.0. See LICENSE.md.