timeBucket Queries

timeBucket queries

Every hypertable model gets a typed timeBucket(...) method: a time_bucket GROUP BY with result-row inference and compile-time column checks. This page covers the query shape; for the aggregate functions you can compute, see Aggregates & hyperfunctions.

const rows = await prisma.sensorReading.timeBucket({
  bucket: "1 hour",
  range: { start, end },          // both bounds required — typing forces you to bound the scan
  where: {
    deviceId: { in: [1, 2] },
    temperature: { gte: 20, lt: 35, not: { equals: 25 } }, // nested `not` is supported
  },
  groupBy: ["deviceId"],
  aggregate: {
    avgTemp: { avg: "temperature" },
    maxTemp: { max: "temperature" },
    samples: { count: "temperature" },
  },
});
// rows: Array<{ bucket: Date; deviceId: number; avgTemp: number; maxTemp: number; samples: number }>

The result row is inferred from groupBy + aggregate. where uses Prisma's where operators (AND/OR/NOT, in, gte, nested not, …). @@map/@map field names are accepted and mapped to DB columns transparently — see Without the generator.

Relation filters (some / none / every / is / isNot)

Filter a hypertable by a related model's fields — compiled to EXISTS subqueries:

where: {
  device: { is: { active: true } },      // to-one: the related device is active
  tags:   { some: { label: "prod" } },   // to-many: has ≥1 matching tag
  alerts: { every: { resolved: true } }, // to-many: all resolved (vacuously true if none)
  owner:  { isNot: null },               // relation exists
}

Results match Prisma's findMany exactly, including every's vacuous truth and NULL handling (is/isNot: null test relation existence). Composite foreign keys are supported, and relation filters nest through other relations to any depth:

// readings whose device also has a reading over 30° (Reading → device → readings)
where: { device: { is: { readings: { some: { temperature: { gt: 30 } } } } } }

The only requirement is that the models you traverse are visible to the generator (or supplied via the manual config).

Ordering & limiting (orderBy / limit)

By default rows come back ordered by the bucket ascending. Use orderBy + limit for "latest N buckets" / "top N by value":

// latest 24 buckets, newest first
await prisma.sensorReading.timeBucket({
  bucket: "1 hour", range: { start, end },
  aggregate: { avgTemp: { avg: "temperature" } },
  orderBy: { bucket: "desc" },
  limit: 24,
});

// the 10 buckets with the highest average — order by an aggregate, with a multi-key tiebreak
await prisma.sensorReading.timeBucket({
  bucket: "1 hour", range: { start, end },
  groupBy: ["deviceId"],
  aggregate: { avgTemp: { avg: "temperature" } },
  orderBy: [{ avgTemp: "desc" }, { bucket: "asc" }],   // array = precedence
  limit: 10,
});

orderBy keys are type-checked to the orderable columns — "bucket", a groupBy column, or an aggregate result name — so a typo or bad direction fails to compile. A single object orders by one (or more) keys; an array gives explicit multi-key precedence. limit is a positive integer (SQL LIMIT), applied after ordering. Ordering composes with gapfill (locf still carries forward correctly under orderBy: { bucket: "desc" }).

Gap-filling empty buckets (gapfill + fill)

By default timeBucket returns only buckets that have rows. Set gapfill: true to emit a row for every bucket across range (time_bucket_gapfill), then fill the empty ones per aggregate:

const rows = await prisma.sensorReading.timeBucket({
  bucket: "1 hour",
  range: { start, end },
  gapfill: true,
  aggregate: {
    avgTemp: { avg: "temperature", fill: "locf" },        // carry the last value forward
    smooth:  { avg: "temperature", fill: "interpolate" }, // linear interpolation
    raw:     { avg: "temperature" },                      // null in empty buckets
  },
});
// rows: Array<{ bucket: Date; avgTemp: number | null; smooth: number | null; raw: number | null }>

• fill: "locf" carries the last observed value forward; fill: "interpolate" linearly interpolates between surrounding values. Both are null at range edges with no neighbour, and gap-filling happens per groupBy group.
• Under gapfill, every aggregate becomes nullable — its base type still follows as / fill.
• fill requires gapfill: true and is mutually exclusive with as.

Time zones & bucket alignment (timezone / origin / offset)

const rows = await prisma.sensorReading.timeBucket({
  bucket: "1 day",
  range: { start, end },
  timezone: "Europe/Stockholm", // day/week/month buckets align to this zone's calendar (DST-aware)
  // origin: new Date("2026-01-01T00:00:00Z"), // align buckets to an instant
  // offset: "6 hours",                        // shift bucket boundaries by an interval
  aggregate: { avgTemp: { avg: "temperature" } },
});

• timezone requires a timestamptz time column (@db.Timestamptz) — calendar bucketing is undefined for a tz-naive column; without it, time_bucket aligns to UTC.
• origin (a Date) and offset (an interval) shift bucket boundaries. origin + offset together require a timezone, and none of timezone / origin / offset combine with gapfill yet.