lode - v0.1.0

Duration types with pog

This guide is about storing durations — spans of time like “90 minutes” or “three days” — rather than instants. Ecto’s own “Duration types with Postgrex” guide has a satisfying ending: Ecto 3.12 added a first-class Duration type that maps directly to a PostgreSQL interval column, so you declare field :reminder, :duration, hand the changeset an Elixir.Duration struct, and Postgrex carries it across the wire as a real interval. You then get interval arithmetic in queries for free.

lode cannot offer that ending today, and the honest thing to do is to say so up front and then show you what does work. This guide covers the gap, two practical workarounds, and where each one stops being enough.

What Ecto does, and why lode does not

In Elixir, the chain is: an Elixir.Duration struct → the Ecto.Type named :duration → Postgrex’s Postgrex.Interval → a Postgres interval value. Every link in that chain exists. Postgrex knows how to encode and decode interval, and Ecto’s :duration type wraps it as a schema field.

lode’s database boundary is the value.Value type in lode/src/lode/value.gleam. Every LodeType mediates between a Gleam type and one of those variants. The full set is:

pub type Value {
  VNull
  VInt(Int)
  VFloat(Float)
  VBool(Bool)
  VString(String)
  VBinary(BitArray)
  VList(List(Value))
  VMap(List(#(String, Value)))
  VDecimal(Decimal)
  VDate(Date)
  VTime(TimeOfDay)
  VNaiveDatetime(date: Date, time: TimeOfDay)
  VTimestamp(Timestamp)
}

There is no VInterval and no VDuration. The temporal LodeTypes in lode/src/lode/types/temporal.gleam cover exactly four shapes — date(), time(), naive_datetime(), and utc_datetime() — and none of them is a span. gleam_time does ship a Duration type in gleam/time/duration, and lode depends on gleam_time, but that Duration is an application-land value: it never appears in Value, so there is no built-in LodeType that loads or dumps it, and pog is never asked to encode it as an interval. The schema/query layer therefore cannot round-trip a Postgres interval column the way Ecto’s :duration type can.

Interval/duration type. temporal.duration() is an LodeType for a Postgres interval that round-trips through the normal bound-parameter path: a read decodes the interval into a VInterval (months/days/microseconds kept separate) and load folds it into a gleam/time/duration.Duration; dump binds it back via pgo’s interval encoder. No inline literals or raw SQL needed. The integer-seconds recipe (1 below) remains a fine alternative when you’d rather store a plain bigint. TASK:duration-type

Both workarounds are real and in daily use; the rest of this guide is the recipes. (For the design rationale behind keeping Value a small, closed, explicitly-tagged set rather than carrying arbitrary driver types, see DESIGN.md in the repository.)

Recipe 1: store seconds (or microseconds) in a bigint

This is the recommended default. A duration is, physically, a count of time units; pick the smallest unit you care about, store it as VInt in a bigint column, and convert to and from gleam/time/duration.Duration at the schema boundary. Nothing about the database knows it is a duration — it sees an integer — but your Gleam code works with proper Duration values everywhere else.

Start with the migration. The column is an ordinary BigInt:

import lode/migration
import lode/migration/ddl

pub fn add_reminders() -> migration.Migration {
  migration.new(20_260_614_120_000)
  |> migration.create_table("reminders", [
    ddl.column("id", ddl.Serial) |> ddl.primary_key,
    ddl.column("label", ddl.Text) |> ddl.not_null,
    // The duration, stored as a whole number of seconds.
    ddl.column("remind_after_seconds", ddl.BigInt) |> ddl.not_null,
  ])
}

The schema field is typed as Int on the Value side, but the struct carries a Duration. The conversion lives in load/dump — dump turns the Duration into seconds, load turns the stored seconds back into a Duration:

import lode/schema.{type Schema}
import lode/types/primitive
import lode/value.{VInt, VString}
import gleam/dict
import gleam/result
import gleam/time/duration.{type Duration}

pub type Reminder {
  Reminder(id: Int, label: String, remind_after: Duration)
}

pub fn reminder_schema() -> Schema(Reminder) {
  schema.new(
    source: "reminders",
    fields: [
      schema.primary_key(name: "id", of: primitive.id()),
      schema.field(name: "label", of: primitive.string()),
      // On the Value side this is a plain integer column.
      schema.field(name: "remind_after_seconds", of: primitive.integer()),
    ],
    load: fn(row) {
      use id <- result.try(schema.require_int(row, "id"))
      use label <- result.try(schema.require_string(row, "label"))
      use secs <- result.try(schema.require_int(row, "remind_after_seconds"))
      // Integer seconds -> Duration as we leave the database.
      Ok(Reminder(id:, label:, remind_after: duration.seconds(secs)))
    },
    dump: fn(r: Reminder) {
      dict.from_list([
        #("id", VInt(r.id)),
        #("label", VString(r.label)),
        // Duration -> integer seconds as we enter the database.
        #("remind_after_seconds", VInt(duration_to_seconds(r.remind_after))),
      ])
    },
  )
}

The one wrinkle is the Duration → seconds conversion. duration.to_seconds returns a Float (a Duration is nanosecond-accurate, and floats lose precision), so for an integer-seconds column take the seconds component directly with to_seconds_and_nanoseconds, which is lossless on every target and truncates the sub-second part:

fn duration_to_seconds(d: Duration) -> Int {
  let #(seconds, _nanoseconds) = duration.to_seconds_and_nanoseconds(d)
  seconds
}

If sub-second precision matters, store microseconds instead. Widen nothing in the schema — bigint already holds it — and adjust only the two conversions:

fn duration_to_micros(d: Duration) -> Int {
  let #(seconds, nanoseconds) = duration.to_seconds_and_nanoseconds(d)
  seconds * 1_000_000 + nanoseconds / 1000
}

fn micros_to_duration(micros: Int) -> Duration {
  let seconds = micros / 1_000_000
  let nanoseconds = { micros % 1_000_000 } * 1000
  duration.add(duration.seconds(seconds), duration.nanoseconds(nanoseconds))
}

Name the column remind_after_micros to keep the unit obvious, and load with micros_to_duration(secs) in place of duration.seconds(secs). Pick the unit once, encode it in the column name, and the choice never leaks past load/ dump.

Querying is the integer story you already know: remind_after_seconds is an ordinary bigint, so query.where with an integer comparison, order_by, and aggregates all work without ceremony — where remind_after_seconds > 3600 finds reminders longer than an hour. What you do not get is the database treating the value as a span: you cannot write created_at + remind_after_seconds and have Postgres read the right-hand side as an interval. That is what recipe 2 is for.

Recipe 2: a real Postgres interval via raw SQL

When you need Postgres itself to do interval arithmetic — now() + <span>, age(...), adding a duration to a timestamptz column inside the query — there is no way around handing Postgres a real interval. lode’s two escape hatches let you do exactly that without inventing a Value variant.

The narrow, ad-hoc route is repo.query_raw, which runs SQL with positional $1 parameters and returns rows keyed by column name (Row = Dict(String, Value)):

import lode/repo.{type Repo}
import lode/error.{type LodeError}
import lode/value.{VInt, type Value}
import gleam/dict
import gleam/list
import gleam/option

// "What instant is `seconds` from now?" computed by Postgres as an interval.
pub fn deadline_from_now(
  r: Repo,
  seconds: Int,
) -> Result(option.Option(Value), LodeError) {
  // make_interval(secs => $1) builds a real interval from the integer we pass;
  // $1 is bound as VInt, so the value is never spliced into the SQL text.
  let sql = "SELECT now() + make_interval(secs => $1) AS deadline"
  use rows <- result.map(repo.query_raw(repo: r, sql:, params: [VInt(seconds)]))
  case rows {
    [row, ..] -> dict.get(row, "deadline") |> option.from_result
    [] -> option.None
  }
}

make_interval(secs => $1) keeps the SQL parameterized: the integer crosses as a bound VInt, and Postgres constructs the interval. The returned deadline column comes back as a VTimestamp (it is a timestamptz), which does have a Value variant, so the result decodes cleanly even though the interval itself never had to.

The other route stays inside the typed query builder: expr.fragment splices raw SQL into an expression tree, with ? standing in for each argument (note: ?, not $n — the builder numbers the parameters for you). This lets a duration computation live inside a normal query.where. Suppose events has a starts_at timestamptz and you want events starting within the next window_seconds:

import lode/query
import lode/query/expr.{type FieldRef, FieldRef}
import lode/types/primitive

// A typed accessor for events.starts_at at binding 0.
fn starts_at() -> FieldRef(Event, Timestamp) {
  FieldRef(0, "starts_at", temporal.utc_datetime())
}

fn starting_within(window_seconds: Int) -> query.Query {
  query.from("events", "e")
  |> query.where(expr.fragment(
    // ? is the column; ? is the bound integer, turned into an interval here.
    "? <= now() + make_interval(secs => ?)",
    [expr.col(starts_at()), expr.Lit(VInt(window_seconds))],
  ))
}

The literal argument renders as a bound parameter, not interpolated text, so this is as injection-safe as any other query. The interval is built and consumed entirely inside Postgres; lode only ever sees the integer going in and Event rows coming back.

If you would rather pass a duration string than an integer, the same shape works with $1::interval and a VString — now() + $1::interval with VString("90 minutes"). Build the string from a gleam_time Duration if you like; duration.to_iso8601_string produces an ISO-8601 duration such as "PT90M", which Postgres accepts when cast to interval. This keeps the Duration authoritative in your code while still handing Postgres a real interval to compute with.

gleam_time durations stay in your code

Worth stating plainly, because it is easy to lose in the divergence: nothing above stops you from using gleam/time/duration.Duration as the currency of your application. Construct durations with duration.seconds, duration.minutes, duration.hours; combine them with duration.add; compare and format them. The schema struct in recipe 1 holds a Duration — the integer column is purely how it is persisted. The only boundary the Duration cannot cross on its own is the database one, and recipes 1 and 2 are the two bridges across it. When the duration never needs to be persisted at all — a timeout, a retry backoff, a computed window passed to recipe 2 — there is no boundary to cross and no workaround to apply; it is just a value.

Choosing between the recipes

When a first-class duration type lands (TASK:duration-type), recipe 1 becomes a one-line field declaration and recipe 2’s fragments become ordinary typed query operators — but the data you wrote with recipe 1 stays readable, because it was only ever integers.

For the read side of schemas and how load/dump fit the wider picture, see Data mapping and validation; for the full list of differences from Elixir’s Ecto, see Divergences from Ecto.

✨ Search Document