SQLite

SQLite has four storage classes — integer, real, text and blob — and no range type, so a Tempo expression cannot be stored there as PostgreSQL stores it. Tempo.Ecto.SQLite.TempoRange keeps it in one TEXT column instead: the expression whole, as it was written, beside the integer bounds of the time it covers. Tempo.Ecto.SQLite.QueryAPI asks Allen's questions of those bounds with plain comparisons, which an index on each bound answers.

Installation

Add ecto_sqlite3 beside ex_tempo_sql; Tempo SQL lists it as an optional dependency, so an application on PostgreSQL alone does not fetch it:

def deps do
  [
    {:ex_tempo_sql, "~> 0.4"},
    {:ecto_sqlite3, "~> 0.26"}
  ]
end

The JSON functions the column is queried with are built into SQLite from 3.38; exqlite, which ecto_sqlite3 runs on, bundles a later one. Nothing is loaded into SQLite: no extension, no function defined from Elixir.

Setup

The migration adds the column and its indexes:

defmodule MyApp.Repo.Migrations.CreateMeetings do
  use Ecto.Migration

  import Tempo.SQL.SQLite.Migration

  def change do
    create table(:meetings) do
      add :name, :string
      add_tempo_range :window
    end

    create_tempo_range_index :meetings, :window
  end
end

The schema names the type, and the frame of a field whose values have no zone:

defmodule MyApp.Meeting do
  use Ecto.Schema

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

defmodule MyApp.Shop do
  use Ecto.Schema

  schema "shops" do
    field :name, :string
    field :hours, Tempo.Ecto.SQLite.TempoRange, frame: :floating
  end
end

What the column holds

The column holds a JSON document. Its lo and hi are the half-open bounds of the stretch the expression covers, in whole seconds; its spans are the bounds of each span; its meta is the expression's ISO 8601 text and metadata, from which it is read back:

iex> import Tempo.Sigils
iex> {:ok, json} = Tempo.SQL.SQLite.Document.encode(~o"2026-06-15T09:00Z/2026-06-15T10:00Z")
iex> document = Tempo.SQL.JSON.decode!(json)
iex> {document["lo"], document["hi"]}
{1781514000, 1781517600}
iex> document["meta"]["expression"]
"2026Y6M15DT9H0MZ/T10H0MZ"
iex> Tempo.SQL.SQLite.Document.decode(json)
{:ok, ~o"2026-06-15T09:00Z/2026-06-15T10:00Z"}

The meeting is stored as the seconds that bound it, and read back as it was written.

• On the UTC frame, the default, a bound is seconds since the Unix epoch at UTC, and a value in a zone or at a time shift is stored as the instants it is.

• On the floating frame, frame: :floating, a bound is seconds since 1970-01-01T00:00 on the wall clock, and a value with no zone and no time shift is stored as the times it is.

• A fraction of a second is rounded outward, so the bounds always cover the expression. The expression itself keeps its fraction.

• An open end is the least or the greatest 64-bit integer, so a comparison needs no case for it.

• A recurrence with no end has its stretch alone, from its first occurrence on.

A value of the other frame is refused, as on PostgreSQL: 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. So is an expression with no place on the time line — a value with no year, a duration, an interval open at both ends.

Queries

A query compares a column with a probe, the bounds of an expression on the column's frame, which Tempo.Ecto.SQLite.QueryAPI.probe/2 makes:

iex> import Tempo.Sigils
iex> Tempo.Ecto.SQLite.QueryAPI.probe(~o"2026-06-15T09:30Z/2026-06-15T09:45Z")
{:ok, %{lo: 1781515800, hi: 1781516700}}
iex> Tempo.Ecto.SQLite.QueryAPI.probe(~o"2026-06-15T09:30/2026-06-15T09:45", :floating)
{:ok, %{lo: 1781515800, hi: 1781516700}}

The probe is pinned with ^, as a range is for the PostgreSQL macros:

import Ecto.Query
import Tempo.Ecto.SQLite.QueryAPI

{:ok, quarter_hour} = probe(~o"2026-06-15T09:30Z/2026-06-15T09:45Z")

Repo.all(from m in MyApp.Meeting, where: overlaps(m.window, ^quarter_hour), select: m.name)

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

Each gives the answer the PostgreSQL operator gives for the same values; the test suite holds the two to each other. overlaps and contains test each span a set holds, so the gaps between its members match nothing.

Indexes

create_tempo_range_index/2 creates an expression index on each bound, json_extract(column, '$.lo') and json_extract(column, '$.hi'). A query that bounds either end of the stretch — every macro does — is answered from one of them, which EXPLAIN QUERY PLAN shows.

SQLite evaluates the indexes on every write, so with them in place it refuses text that is not JSON in the column at all.

What it is not

• Not a range type. Set operations on stored expressions — a union of two rows, the free time between them — are Tempo's, after loading; SQL sees the bounds.

• Not finer than a second. Two spans within one second can match one probe.

• Not an R*Tree. The bounds are B-tree indexes. For a large table of spans of very different lengths, an R*Tree index may answer an overlap faster; the design in the repository's plans/sqlite.md sets out why it is not the default.