# `Tempo.SQL.Conversion`
[🔗](https://github.com/elixir-tempo/tempo_sql/blob/v0.4.0/lib/tempo/sql/conversion.ex#L1)

Internal helpers that translate between Tempo intervals and the
`DateTime` / `:unbound` values that `Postgrex.Range` understands.

The storage contract this module enforces — see the README for
the full rationale:

  * An interval is stored as the instants it runs between
    (`Tempo.Interval.instants/1`): from the instant it begins to
    the instant it ends, half-open. An end written to a date is read
    through it, so `2026-06-15/2026-06-20` is stored up to the 21st.

  * A column is on one frame: a `tstzrange` holds instants on the UTC
    line, a value in a zone or at a time shift; a `tsrange` holds
    floating instants, a value with neither. A value of the other frame
    is refused, never placed on this one.

  * Qualifications (`:uncertain`, `:approximate`) and calendars other
    than the Gregorian are rejected by the plain types, which cannot
    keep them; the composite types keep them in their `meta` column.

  * A recurrence, or anything else of several spans, is stored by
    `Tempo.Ecto.IntervalSet` as a `tstzmultirange`, not by the plain
    interval types.

# `frame`

```elixir
@type frame() :: :utc | :floating
```

The line a column's instants lie on: `:utc` for a `timestamptz` range,
whose values are in a zone or at a time shift, or `:floating` for a
`timestamp` range, whose values have neither.

# `resolution`

```elixir
@type resolution() :: :year | :month | :day | :hour | :minute | :second
```

A unit a loaded range's ends are written to.

# `as_interval`

```elixir
@spec as_interval(Tempo.t()) ::
  {:ok, Tempo.t()} | {:error, Tempo.SQL.UnsupportedValueError.t()}
```

The one interval an expression is: an interval itself, or a value as
the interval it names.

### Arguments

* `expression` is a `t:Tempo.t/0`.

### Returns

* `{:ok, interval}`, a `t:Tempo.t/0` interval.

* `{:error, exception}` for an expression of several spans (a value
  naming several, a recurrence, a set) or with none.

### Examples

    iex> Tempo.SQL.Conversion.as_interval(~o"2027-06")
    {:ok, ~o"2027-06/2027-06"}

    iex> {:error, error} = Tempo.SQL.Conversion.as_interval(~o"R3/2027-06-01/P1D")
    iex> error.reason
    :multi_valued_slot

# `interval_to_queryable_range`

```elixir
@spec interval_to_queryable_range(Tempo.t() | Tempo.Duration.t(), frame()) ::
  {:ok, Postgrex.Range.t()}
  | {:error, Tempo.SQL.UnsupportedValueError.t() | Exception.t()}
```

Converts any expression into a `%Postgrex.Range{}` tolerant enough
for the composite `tempo_range` and `tempo_floating_range` types.

Unlike `interval_to_range/2`, this accepts what the plain range
encoder rejects — qualifications, calendars other than the Gregorian,
values that name several, recurrences — because the composite's
`meta` column carries the expression losslessly. The range is the
stretch the expression covers, from its first instant to its last, so
Postgres range operators still find it.

### Arguments

* `expression` is a `t:Tempo.t/0`.

* `frame` is `:utc` (the default) for an expression in a zone or at a
  time shift, or `:floating` for one with neither.

### Returns

* `{:ok, range}` where `range` is a `t:Postgrex.Range.t/0`. An open
  end, or a recurrence with no end, becomes `:unbound`, so it is still
  queryable.

* `{:error, exception}` when the expression has no place on the time
  line at all — a bare duration, a value with no year, a span open at
  both ends, or a recurrence counted back with no start — or, with
  the reason `:frame`, when it is of the other frame.

* `{:error, %ArgumentError{}}` when `frame` is not one of
  `valid_frames/0`.

### Examples

    iex> workday = Tempo.Interval.new!(
    ...>   from: ~o"2027-06-15T09:00:00Z",
    ...>   to: ~o"2027-06-15T17:00:00Z"
    ...> )
    iex> {:ok, range} = Tempo.SQL.Conversion.interval_to_queryable_range(workday)
    iex> range.upper
    ~U[2027-06-15 17:00:00Z]

An open-ended interval keeps a usable lower bound:

    iex> {:ok, range} = Tempo.SQL.Conversion.interval_to_queryable_range(~o"2027-06-15T09:00:00/..", :floating)
    iex> {range.lower, range.upper}
    {~N[2027-06-15 09:00:00], :unbound}

# `interval_to_range`

```elixir
@spec interval_to_range(Tempo.t(), frame()) ::
  {:ok, Postgrex.Range.t()}
  | {:error, Tempo.SQL.UnsupportedValueError.t() | Exception.t()}
```

Converts an interval into a `%Postgrex.Range{}` on a frame: `DateTime`
bounds at UTC for a `tstzrange`, or `NaiveDateTime` bounds for a
`tsrange`.

The range is half-open — lower inclusive, upper exclusive — from the
instant the interval begins to the instant it ends, so no boundary is
gained or lost in storage.

### Arguments

* `interval` is a `t:Tempo.t/0` interval.

* `frame` is `:utc` (the default) for an interval in a zone or at a
  time shift, or `:floating` for one with neither.

### Returns

* `{:ok, range}` where `range` is a `t:Postgrex.Range.t/0`.

* `{:error, exception}` where `exception` is a
  `t:Tempo.SQL.UnsupportedValueError.t/0`, when the interval holds
  something the range cannot — see the storage-contract guide. Its
  reason is `:frame` for an interval of the other frame.

* `{:error, %ArgumentError{}}` when `frame` is not one of
  `valid_frames/0`.

### Examples

    iex> workday = Tempo.Interval.new!(
    ...>   from: ~o"2027-06-15T09:00:00Z",
    ...>   to: ~o"2027-06-15T17:00:00Z"
    ...> )
    iex> {:ok, range} = Tempo.SQL.Conversion.interval_to_range(workday)
    iex> {range.lower, range.upper}
    {~U[2027-06-15 09:00:00Z], ~U[2027-06-15 17:00:00Z]}
    iex> {range.lower_inclusive, range.upper_inclusive}
    {true, false}

    iex> {:ok, range} = Tempo.SQL.Conversion.interval_to_range(~o"2027-06-15/2027-06-20", :floating)
    iex> {range.lower, range.upper}
    {~N[2027-06-15 00:00:00], ~N[2027-06-21 00:00:00]}

    iex> {:error, error} = Tempo.SQL.Conversion.interval_to_range(~o"2027-06-15/2027-06-20")
    iex> error.reason
    :frame

    iex> {:error, error} = Tempo.SQL.Conversion.interval_to_range(~o"1984?/2004", :floating)
    iex> error.reason
    :qualification

# `intervals`

```elixir
@spec intervals(Tempo.t()) ::
  {:ok, [Tempo.t()]} | {:error, Tempo.SQL.UnsupportedValueError.t()}
```

The intervals an expression covers, each a span, in order.

### Arguments

* `expression` is a `t:Tempo.t/0` with a last span.

### Returns

* `{:ok, intervals}`, a list of `t:Tempo.t/0` intervals.

* `{:error, exception}` for an expression with no end or no spans.

### Examples

    iex> Tempo.SQL.Conversion.intervals(~o"R2/2027-06-01/P1D")
    {:ok, [~o"2027-06-01/2027-06-01", ~o"2027-06-02/2027-06-02"]}

# `on_frame`

```elixir
@spec on_frame(Tempo.t(), frame()) ::
  :ok | {:error, Tempo.SQL.UnsupportedValueError.t() | Exception.t()}
```

Checks that an expression lies on a column's frame: on the UTC line,
in a zone or at a time shift, for `:utc`, or floating, with a year and
neither, for `:floating`.

The frame is read from the instants the expression would be stored
as, so a value passes as `interval_to_queryable_range/2` would place it.
An expression with no place on the time line, such as one with no
year, has no frame to check; the encoders refuse it when it is stored.

### Arguments

* `expression` is a `t:Tempo.t/0`.

* `frame` is `:utc` or `:floating`.

### Returns

* `:ok` when the expression is on the frame, or on a cycle.

* `{:error, exception}`, a `t:Tempo.SQL.UnsupportedValueError.t/0` with
  the reason `:frame`, when it is on the other frame, or an
  `ArgumentError` when `frame` is not one of `valid_frames/0`.

### Examples

    iex> Tempo.SQL.Conversion.on_frame(~o"2027-06-15T09:00Z", :utc)
    :ok

    iex> {:error, error} = Tempo.SQL.Conversion.on_frame(~o"2027-06-15T09:00", :utc)
    iex> error.reason
    :frame

    iex> Tempo.SQL.Conversion.on_frame(~o"T09/T17", :floating)
    :ok

# `range_to_interval`

```elixir
@spec range_to_interval(Postgrex.Range.t(), keyword()) ::
  {:ok, Tempo.t()} | {:error, Exception.t()}
```

Convert a `%Postgrex.Range{}` back into an interval.

Unlike discrete range types (`int4range`, `daterange`), PostgreSQL
does **not** canonicalise `tstzrange` to `[lower, upper)` on
output — a range written as `[a, b]` round-trips as `[a, b]`.
This loader therefore normalises any non-half-open range to
`[lower, upper)` by shifting the offending endpoint one second:

  * `[a, b]` → `[a, b + 1s)`.

  * `(a, b)` → `[a + 1s, b)`.

  * `(a, b]` → `[a + 1s, b + 1s)`.

### Arguments

* `range` is the `t:Postgrex.Range.t/0` to convert.

* `options` is a keyword list of options.

### Options

* `:resolution` truncates both endpoints to the given component,
  dropping all sub-components. Must be one of `:year`, `:month`,
  `:day`, `:hour`, `:minute`, or `:second`. Defaults to `:second`
  (no truncation). An end at a date resolution is read through it,
  so the range's upper bound becomes the last day, month or year it
  holds. See the storage contract guide for semantics.

### Returns

* `{:ok, interval}` where `interval` is a `t:Tempo.t/0` interval.

* `{:error, exception}` when an endpoint is not a time, or when
  truncating to the requested resolution leaves the interval ending
  before it begins.

### Examples

    iex> range = %Postgrex.Range{
    ...>   lower: ~U[2027-06-15 09:00:00Z],
    ...>   upper: ~U[2027-06-15 17:00:00Z],
    ...>   lower_inclusive: true,
    ...>   upper_inclusive: false
    ...> }
    iex> {:ok, interval} = Tempo.SQL.Conversion.range_to_interval(range)
    iex> {Tempo.hour(Tempo.Interval.from(interval)), Tempo.hour(Tempo.Interval.to(interval))}
    {9, 17}

A range of whole days loaded at the day is the days it holds:

    iex> days = %Postgrex.Range{
    ...>   lower: ~U[2027-06-15 00:00:00Z],
    ...>   upper: ~U[2027-06-21 00:00:00Z],
    ...>   lower_inclusive: true,
    ...>   upper_inclusive: false
    ...> }
    iex> Tempo.SQL.Conversion.range_to_interval(days, resolution: :day)
    {:ok, ~o"2027Y6M15DZ/2027Y6M20DZ"}

# `truncate_tempo`

```elixir
@spec truncate_tempo(Tempo.t(), atom()) :: Tempo.t()
```

Truncates a value to the given resolution, dropping all finer
components.

### Arguments

* `tempo` is the `t:Tempo.t/0` value to truncate.

* `resolution` is the coarsest component to keep — one of those
  returned by `valid_resolutions/0`.

### Returns

* A `t:Tempo.t/0`. A value already coarser than the requested
  resolution is returned unchanged.

### Examples

    iex> Tempo.SQL.Conversion.truncate_tempo(~o"2027-06-15T09:30:45", :day)
    ~o"2027Y6M15D"

    iex> Tempo.SQL.Conversion.truncate_tempo(~o"2027Y", :second)
    ~o"2027Y"

# `valid_frames`

```elixir
@spec valid_frames() :: [frame(), ...]
```

Returns the valid values for the `:frame` option on the Ecto types.

### Returns

* A list of frame atoms, the default first.

### Examples

    iex> Tempo.SQL.Conversion.valid_frames()
    [:utc, :floating]

# `valid_resolutions`

```elixir
@spec valid_resolutions() :: [resolution(), ...]
```

Returns the valid values for the `:resolution` option on the Ecto
types.

### Returns

* A list of resolution atoms, coarsest first.

### Examples

    iex> Tempo.SQL.Conversion.valid_resolutions()
    [:year, :month, :day, :hour, :minute, :second]

# `validate_frame!`

```elixir
@spec validate_frame!(term()) :: frame()
```

Validates a `:frame` option.

### Arguments

* `frame` is the value given as the `:frame` option.

### Returns

* The frame atom unchanged, when it is one of those returned by
  `valid_frames/0`.

* Raises `ArgumentError` otherwise, naming the value it was given.

### Examples

    iex> Tempo.SQL.Conversion.validate_frame!(:floating)
    :floating

    iex> Tempo.SQL.Conversion.validate_frame!(:local)
    ** (ArgumentError) expected :frame to be one of [:utc, :floating], got: :local

# `validate_resolution!`

```elixir
@spec validate_resolution!(term()) :: resolution()
```

Validates a `:resolution` option value.

### Arguments

* `resolution` is the value to validate.

### Returns

* The resolution atom unchanged, when it is one of those returned by
  `valid_resolutions/0`.

* Raises `ArgumentError` otherwise, naming the value it was given.

### Examples

    iex> Tempo.SQL.Conversion.validate_resolution!(:day)
    :day

    iex> Tempo.SQL.Conversion.validate_resolution!(:fortnight)
    ** (ArgumentError) expected :resolution to be one of [:year, :month, :day, :hour, :minute, :second], got: :fortnight

---

*Consult [api-reference.md](api-reference.md) for complete listing*