# Tempo SQL

[![Hex.pm](https://img.shields.io/hexpm/v/ex_tempo_sql.svg)](https://hex.pm/packages/ex_tempo_sql)

Ecto types, query macros and migration helpers that store [Tempo](https://hexdocs.pm/ex_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`:

```elixir
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:

```elixir
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`:

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

## Quick start

### 1. Migration

```elixir
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

```elixir
defmodule MyApp.Meeting do
  use Ecto.Schema

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

### 3. Insert and query

```elixir
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:**

| Type                       | Stores                                                         | Postgres column    |
|----------------------------|----------------------------------------------------------------|--------------------|
| `Tempo.Ecto.Interval`      | an expression of one span: a value or an interval              | `tstzrange`, or `tsrange` when floating |
| `Tempo.Ecto.IntervalSet`   | an expression with a last span: a set, a bounded recurrence    | `tstzmultirange`, or `tsmultirange` when floating |
| `Tempo.Ecto.Tempo`         | as `Tempo.Ecto.Interval`, under the name kept for older schemas | `tstzrange`, or `tsrange` when floating |

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

| Type                          | Stores                                     | Postgres column    |
|-------------------------------|--------------------------------------------|--------------------|
| `Tempo.Ecto.TempoRange`       | any expression with ISO 8601 text          | `tempo_range`, or `tempo_floating_range` when floating |
| `Tempo.Ecto.TempoMultirange`  | any expression with ISO 8601 text          | `tempo_multirange`, or `tempo_floating_multirange` when floating |

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](https://hexdocs.pm/ex_tempo_sql/storage-contract.html#composite-mode--tempo_range-and-tempo_multirange) 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.

```elixir
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):

```elixir
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](https://hexdocs.pm/ex_tempo_sql/storage-contract.html#preserving-resolution--the-options) for the caveats.

## Query API

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

| Macro                      | Postgres operator | Meaning                                              |
|----------------------------|-------------------|------------------------------------------------------|
| `contains(a, b)`           | `@>`              | `a` fully contains `b`                               |
| `overlaps(a, b)`           | `&&`              | `a` and `b` share any instant                        |
| `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:

```elixir
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**](https://hexdocs.pm/ex_tempo_sql/storage-contract.html). 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](#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:

```elixir
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](https://hexdocs.pm/ex_tempo_sql/sqlite.html).

## 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](https://hexdocs.pm/ex_tempo_sql/storage-contract.html) — how each expression maps to a range, what survives a round trip, what is refused, and the composite types.

* [SQLite](https://hexdocs.pm/ex_tempo_sql/sqlite.html) — the SQLite type, what its column holds, its query macros and its indexes.

* [Changelog](https://hexdocs.pm/ex_tempo_sql/changelog.html) — release history.

Full API documentation is available on [HexDocs](https://hexdocs.pm/ex_tempo_sql).

## License

The code is licensed under the Apache License 2.0; [LICENSE.md](https://github.com/elixir-tempo/tempo_sql/blob/v0.4.0/LICENSE.md) gives it.