Tempo SQL

Ecto types, query macros and migration helpers that store Tempo expressions in PostgreSQL tstzrange and tstzmultirange columns, or tsrange and tsmultirange columns for floating values, and in SQLite as JSON with integer bounds.

Tempo models time as intervals, not instants. PostgreSQL models the same shape natively — a tstzrange is 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 takes part in Postgres range queries (@>, &&, -|-) as a first-class value.

Features

• Plain ranges — Tempo.Ecto.Interval and Tempo.Ecto.IntervalSet store an expression's spans as tstzrange and tstzmultirange, queried with Postgres's range operators and GiST indexes.

• Two frames — a column on the UTC line (tstzrange) holds values in a zone or at a time shift, and a floating column (tsrange, declared frame: :floating) holds values with neither, such as opening hours. A value of the other frame is refused, never placed on the wrong line.

• Whole expressions — the composite tempo_range and tempo_multirange types keep an expression as it was written, its resolution, qualifications, recurrence, calendar, zone and metadata, beside the range it covers.

• SQLite — Tempo.Ecto.SQLite.TempoRange keeps an expression whole in a TEXT column beside the integer bounds it covers, queried by Tempo.Ecto.SQLite.QueryAPI with the same Allen-named macros and indexed by expression indexes. No extension is loaded.

• Allen-named queries — Tempo.Ecto.QueryAPI and Tempo.Ecto.QueryAPI.Composite give contains/2, overlaps/2, meets/2, strictly_before/2 and strictly_after/2 over the range operators.

• Migration helpers — Tempo.SQL.Migration adds the columns, their GiST indexes and the composite types.

• An explicit storage contract — what a range cannot hold, an uncertain date, a recurrence with no end, a value with no year, is refused by dump/1 rather than stored approximately.

Supported Elixir and OTP versions

Tempo SQL supports Elixir 1.17 and later on Erlang/OTP 27 and later, inherited from Tempo, with PostgreSQL 14 and later for tstzmultirange (tstzrange alone works from PostgreSQL 9.2), and SQLite 3.38 and later through ecto_sqlite3.

Installation

Add ex_tempo_sql to the dependencies in mix.exs:

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

A value in a named zone is stored as the instants it is, which needs a time zone database that knows the zone. Tz comes with Tempo:

config :elixir, :time_zone_database, Tz.TimeZoneDatabase

A repository with composite columns names the Postgrex types that encode their jsonb field with Erlang's :json:

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

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

iex> import Ecto.Query
iex> import Tempo.Ecto.QueryAPI
iex> import Tempo.Sigils
iex> standup = ~o"2026-06-15T09:00Z/2026-06-15T10:00Z"
iex> Repo.insert!(%MyApp.Meeting{name: "Standup", window: standup})
iex> {:ok, search} = Tempo.Ecto.Interval.dump(~o"2026-06-15T09:30Z/2026-06-15T09:45Z")
iex> Repo.all(from m in MyApp.Meeting, where: overlaps(m.window, ^search), select: m.name)
["Standup"]

The meetings whose window overlaps the quarter hour from half past nine.

Ecto types

Plain range — the span only:

Composite — the whole expression, read back as it was written:

Use the composite types when the plain-range mode's losses hurt — recurrences, qualifications (:uncertain, :approximate), calendars other than the Gregorian, zones, metadata, or the resolution a value was written to. The composite stores the stretch the expression covers, so range queries still find it, beside its ISO 8601 text. See the storage contract guide for setup and trade-offs.

An end written to a date is read through it, so a value or an interval is stored as the instants it runs between: ~o"2026-06-15/2026-06-20" is the six days, the range [2026-06-15 00:00, 2026-06-21 00:00), and ~o"2026" is the year, [2026-01-01 00:00, 2027-01-01 00:00). A plain range keeps no resolution, so the year comes back as the instants that bound it unless the column declares one.

The :frame field option

Every type takes a :frame field option naming the line its column's values lie on. The default, :utc, is a timestamptz column: it holds a value in a zone or at a time shift as the UTC instants it is. :floating is a timestamp column: it holds a value with no zone and no time shift, as written, the same wall-clock times wherever it is read.

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

A floating value given to a UTC column, or a zoned one to a floating column, is refused — by Ecto.Changeset.cast/4 as an error on the field that names the frame to declare, and by dump for a value inserted without a changeset. Tempo does not compare or combine values of two frames, so neither is placed on the other's line.

The :resolution field option

All three plain types accept a :resolution field option that writes loaded ends to a named unit — :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   # to the second
end

A column declared resolution: :year loads a range of whole years as the years it holds (2026/2026 for the range of 2026), whatever 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:

| meets(a, b) | -|- | a is immediately adjacent to b | | strictly_before(a, b) | << | a ends strictly before b starts (Allen precedes) | | strictly_after(a, b) | >> | a starts strictly after b ends |

Migration helpers

Tempo.SQL.Migration exposes, among others, these macros:

add_interval :window                # → add :window, :tstzrange
add_interval_set :busy_times        # → add :busy_times, :tstzmultirange
add_interval :hours, frame: :floating     # → add :hours, :tsrange
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. create_tempo_types/1, add_tempo_range/2 and add_tempo_multirange/2 do the same for the composite types, frame: :floating making their floating counterparts.

Storage contract

Not every Tempo expression can be stored as a Postgres range. Tempo expresses things that tstzrange / tstzmultirange cannot: qualifications, recurrences with no end, calendars other than the Gregorian, values with no year. The plain types are explicit about this — rather than silently lose information, they return :error from dump/1, which surfaces as an Ecto.ChangeError at insert time.

For the full mapping — what is kept, what is dropped, what is refused — see the Storage contract guide. The summary:

What is storable

• A value or an interval with a year under Tempo.Ecto.Interval, stored as the instants it runs between.

• An interval open at one end — ~o"2026-06-15/.." or ~o"../2026-06-15" — as a range unbounded on that side.

• A set, a bounded recurrence, or a value that names several (~o"2026Y{6,8}M") under Tempo.Ecto.IntervalSet, one range a span. Postgres keeps a multirange's adjacent ranges as one, so a recurrence's back-to-back occurrences come back joined.

• A value at a time shift (Z, +02:00), or in a named zone with a time zone database configured (see Installation), stored as the UTC instants it is.

• A floating value, with no zone and no time shift, in a column declared frame: :floating, stored as the wall-clock times it is.

What is not storable by the plain types (returns :error)

• An expression of several spans under Tempo.Ecto.Interval, and a recurrence with no end under Tempo.Ecto.IntervalSet.

• An interval unbounded on both sides.

• A value of the other frame: a floating value in a column on the UTC line, or a value in a zone or at a time shift in a floating column.

• A qualified value (:uncertain, :approximate) — Postgres ranges have no notion of uncertainty.

• A value of a calendar other than the Gregorian — convert it first with Tempo.to_calendar/2.

• A value with no year (~o"T09/T17"), which has no instant to store.

• An empty set — use a NULL column if you need "no set".

What is lossy on round-trip

A plain range keeps the span and nothing else. A loaded value is Gregorian, at UTC or floating as its column is, its ends to the second or to the column's :resolution. Lost on the way are the expression's metadata, its zone (an expression in a zone comes back at UTC), and the resolution it was written to. The composite types keep all of these.

SQLite

SQLite has no range type, so Tempo.Ecto.SQLite.TempoRange stores an expression whole in a TEXT column, as a JSON document holding its ISO 8601 text beside the bounds of the time it covers, in whole seconds. Add {:ecto_sqlite3, "~> 0.26"} to the dependencies, then:

create table(:meetings) do
  add_tempo_range :window          # Tempo.SQL.SQLite.Migration
end

create_tempo_range_index :meetings, :window

{:ok, quarter_hour} = Tempo.Ecto.SQLite.QueryAPI.probe(~o"2026-06-15T09:30Z/2026-06-15T09:45Z")
Repo.all(from m in Meeting, where: overlaps(m.window, ^quarter_hour), select: m.name)

The macros answer as the PostgreSQL range operators do, and the :frame option works as it does there. See the SQLite guide.

Testing against a live database

The test suite needs a PostgreSQL 14+ instance. config/test.exs connects to the database tempo_sql_test as PGUSER (else the current user), with PGPASSWORD, on PGHOST (else localhost). The SQLite tests use a file in the system's temporary directory and need no server. mix test is aliased to run ecto.drop, ecto.create and ecto.migrate for both before the suite.

Documentation

• Storage contract — how each expression maps to a range, what survives a round trip, what is refused, and the composite types.

• SQLite — the SQLite type, what its column holds, its query macros and its indexes.

• Changelog — release history.

Full API documentation is available on HexDocs.

License

The code is licensed under the Apache License 2.0; LICENSE.md gives it.