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, "~> 0.3"},
    {: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+.

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.

• Ordinal-date (year: 2026, day: 75) or week-date (year: 2026, week: 10, day_of_week: 3) endpoints — materialise to a calendar date first via Tempo.to_date/1 / Tempo.to_interval/1.

• 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.