Calendar Buckets

Group rows by the hour, day, week, month, quarter or year of a timestamp, in the time zone your users live in. A calendar bucket is a computed $select entry of a grouped query: the database turns each timestamp into the local date its period starts on (for an hour, the local date and hour), and the query groups, sorts and filters by it. Available on every adapter since 0.1.132; hour since 0.1.147.

typescript

const weekly = await tickets.aggregate({
  filter: { openedAt: { $gte: from, $lt: to } },
  controls: {
    $select: [
      { $bucket: "week", $field: "openedAt", $tz: "Europe/Berlin", $weekStart: "sun", $as: "week" },
      { $fn: "count", $field: "*", $as: "n" },
    ],
    $groupBy: ["week"],
    $sort: { week: 1 },
  },
});
// [{ week: "2026-03-01", n: 12 }, { week: "2026-03-08", n: 9 }, …]

Over HTTP the same query is:

bash

curl "http://localhost:3000/tickets/query?openedAt>=1772323200000&\$select=bucket(openedAt,week,'Europe/Berlin',sun):week,count(*):n&\$groupBy=week&\$sort=week"

The bucket entry

Key	Required	Values	Default
`$bucket`	yes	`'hour' \| 'day' \| 'week' \| 'month' \| 'quarter' \| 'year'`	—
`$field`	yes	a `number.timestamp` field path	—
`$tz`	no	a canonical IANA time zone (`'Europe/Berlin'`)	`'UTC'`
`$weekStart`	no	`'mon' \| 'tue' \| 'wed' \| 'thu' \| 'fri' \| 'sat' \| 'sun'` — unit `week` only	`'mon'`
`$as`	no	the output key	`{unit}_{field}`

The alias is how the rest of the query refers to the bucket:

$groupBy must list it — a bucket in $select that is not in $groupBy is rejected.
$sort orders by it; labels sort chronologically.
$having filters on it, as a string: { week: { $gte: "2026-03-01" } }.
Group by several buckets, or a bucket plus plain fields: $groupBy: ["week", "status"].

Buckets exist only in grouped queries. findMany() and $select without $groupBy reject them.

URL form

In $select, write bucket(field,unit[,tz][,weekStart])[:alias]:

URL entry	Object entry
`bucket(openedAt,day)`	`{ $bucket: "day", $field: "openedAt" }` — key `day_openedAt`
`bucket(openedAt,month,'Europe/Berlin'):m`	`{ $bucket: "month", $field: "openedAt", $tz: "Europe/Berlin", $as: "m" }`
`bucket(openedAt,week,,sun):week`	UTC weeks starting on Sunday (an empty zone slot means the default)
`bucket(openedAt,hour,'Asia/Kolkata'):h`	`{ $bucket: "hour", $field: "openedAt", $tz: "Asia/Kolkata", $as: "h" }`

Quote the zone when it contains / ('America/New_York'); UTC may stay bare. Quote a label in $having, since it contains hyphens: $having=week>='2026-03-01'. The full grammar is on URL Query Syntax.

The label

Every bucket value is a string YYYY-MM-DD: the local calendar date of the bucket's first day, in the bucket's zone. The format is the same for every unit except hour, whose label adds the local hour, YYYY-MM-DDTHH:00:

Unit	Label for a ticket opened on Sunday 2026-03-29, 14:00 in Berlin
`hour`	`2026-03-29T14:00`
`day`	`2026-03-29`
`week` (`mon`)	`2026-03-23`
`week` (`sun`)	`2026-03-29`
`month`	`2026-03-01`
`quarter`	`2026-01-01`
`year`	`2026-01-01`

What follows from the contract:

Days are local. An instant belongs to the day it falls on in $tz, DST included — 2026-03-29T23:30:00Z is day 2026-03-30 in Berlin and 2026-03-29 in UTC.
Labels are wall-clock times in the bucket's zone, not instants. For the instant a bucket starts use bucketStartInstant(label, tz) (Filling gaps) — new Date(label) reads the label in the runtime's own zone. Hours are local wall-clock hours — see Hour buckets.
Labels sort as strings. $sort and $having comparisons need no date parsing.
A week label can fall in the previous month or year. Monday-start week of 2027-01-01 is 2026-12-28.
Quarters start in January, April, July and October. Fiscal years are not supported.
null source → null label. Rows whose timestamp is null or missing form one null group. So do instants outside the supported range, 1970-01-02T00:00:00Z (inclusive) to 3000-01-01T00:00:00Z (exclusive).

Result typing follows: the label is string, or string | null when the source field is optional.

Hour buckets (since 0.1.147)

An hour bucket is the hour on the clock in $tz, labelled YYYY-MM-DDTHH:00. It follows the same rule as day — an instant belongs to the period it falls in locally — so:

Zones with a non-whole-hour offset turn hours off the UTC hour. In Asia/Kolkata (+05:30) hour 10 runs 04:30Z–05:30Z; in Asia/Kathmandu (+05:45) 04:15Z–05:15Z; in America/St_Johns (−03:30, −02:30 in summer) at :30 past the UTC hour.
DST fall-back: the repeated hour is one label covering both passes. In Europe/Berlin on 2026-10-25, 2026-10-25T02:00 holds 00:00Z–02:00Z (02:00–03:00 CEST, then 02:00–03:00 CET) — two hours of rows. The 25-hour fall-back day works the same way.
DST spring-forward: the skipped hour has no label. In Europe/Berlin on 2026-03-29, 2026-03-29T01:00 is followed by 2026-03-29T03:00. A 30-minute shift (Australia/Lord_Howe) leaves a half-hour bucket instead of none.

Every adapter applies this rule, so the same rows give the same labels on PostgreSQL, MySQL, SQLite, MongoDB and memory. If a chart needs equal-length intervals across a DST change, bucket in UTC and convert on the client.

typescript

const hourly = await tickets.aggregate({
  filter: { openedAt: { $gte: from, $lt: to } },
  controls: {
    $select: [
      { $bucket: "hour", $field: "openedAt", $tz: "Europe/Berlin", $as: "hour" },
      { $fn: "count", $field: "*", $as: "n" },
    ],
    $groupBy: ["hour"],
    $sort: { hour: 1 },
    $having: { hour: { $gte: "2026-10-25T00:00" } },
  },
});
// [{ hour: "2026-10-25T01:00", n: 7 }, { hour: "2026-10-25T02:00", n: 15 }, { hour: "2026-10-25T03:00", n: 6 }, …]

Time zones

IANA names only, as listed by the Node.js runtime (Intl.supportedValuesOf("timeZone")), plus UTC and the Etc/GMT±N zones.
Case-insensitive and canonicalized: europe/berlin is accepted as Europe/Berlin.
Aliases are rejected with a hint: US/Eastern → Time zone "US/Eastern" is an alias — use "America/New_York". The same goes for Asia/Calcutta, Etc/UTC and CET.
Offsets and abbreviations are rejected: +02:00, CEST.

Each database resolves the zone with its own time zone data (see Adapter notes). Labels agree across adapters wherever those databases agree for the dates involved.

Which fields can be bucketed

The source must be a field declared as a timestamp: number.timestamp, number.timestamp.created or number.timestamp.updated. No annotation opts a field in — the type is the declaration. The field must also:

be a stored leaf — not inside a @db.json column or an array, on any adapter;
not be @db.encrypted (ENC_FIELD_AGG) or, over HTTP, @db.writeOnly;
be filterable by the adapter (every bundled adapter can filter a timestamp column);
be a dimension, when the table declares @db.column.dimension / @db.column.measure (strict mode);
belong to a table whose adapter supports calendar buckets at all.

The programmatic API, the HTTP gate and /meta apply these rules through one function, so they always agree and report the same reason — the first rule a field fails, in the order listed (encryption first, then JSON, type, filterability, dimension, adapter). Custom gates can call it too: bucketSourceVerdict.

A dotted source (stats.firstSeenAt) needs an explicit $as. An alias must look like an identifier (^[A-Za-z_][A-Za-z0-9_]*$), be unique in $select, and not equal a field name of the table.

Discovering support through `/meta`

A UI can build its time-grouping controls from the table's /meta:

json

{
  "bucketUnits": ["hour", "day", "week", "month", "quarter", "year"],
  "fields": {
    "openedAt": { "sortable": true, "filterable": true, "bucketable": true },
    "points": { "sortable": true, "filterable": true }
  }
}

bucketUnits lists the units the adapter supports, smallest first. It is omitted when there are none. A custom adapter may lack hour — offer hourly grouping only when it is listed.
fields[path].bucketable: true is present exactly when the server accepts a bucket over that field.

Filling gaps

Groups exist only where rows exist, so a chart of weekly counts has holes for empty weeks. @atscript/db-client re-exports the label helpers from @uniqu/core to fill them:

bucketSeries(first, last, unit, { weekStart?, tz?, maxLength? }) — every label from the bucket holding first through the one holding last, inclusive: the full axis of the chart. Pass the query's unit, $weekStart and $tz. With tz, labels that never occur in that zone — the hour a DST spring-forward skips — are left out, so the axis holds exactly the labels the server can return. Throws a RangeError past maxLength labels (default 100 000).
nextBucketLabel(label, unit, { weekStart?, tz? }) — the label of the following bucket, one step. Zone-free unless tz is given; pass tz for hour. (The third argument may also be just the week start — the older form.)
bucketStartInstant(label, tz) — the first instant (epoch ms) of that local date or hour, for a time axis. On a day whose midnight is skipped by DST, it returns the first instant that exists that day. A repeated hour starts at its first pass; a skipped hour returns the transition instant (the start of the next hour).

typescript

import { bucketSeries } from "@atscript/db-client";

const counts = new Map(weekly.map((row) => [row.week, row.n]));
const series = bucketSeries(weekly[0].week, weekly.at(-1)!.week, "week", { weekStart: "sun" }).map(
  (week) => ({ week, n: counts.get(week) ?? 0 }),
);

bucketSeries("2026-03-29T00:00", "2026-03-29T04:00", "hour", { tz: "Europe/Berlin" });
// ["2026-03-29T00:00", "2026-03-29T01:00", "2026-03-29T03:00", "2026-03-29T04:00"]
// without tz, "2026-03-29T02:00" would appear — an hour that never occurs in Berlin

Performance

A bucket is an expression over the timestamp, so grouping by it cannot use an index. Always bound the scan with a range filter on the raw timestamp, which can:

typescript

filter: { openedAt: { $gte: Date.parse("2026-01-01T00:00:00+01:00"), $lt: Date.parse("2026-04-01T00:00:00+02:00") } }

Don't filter on the label with $having to restrict the period — $having runs after every row has been grouped.

Errors

Situation	Error	HTTP
Unknown unit, week start or zone; alias zone; `$weekStart` with a unit other than `week`	`INVALID_QUERY`	400
Bucket outside a grouped query, missing from `$groupBy`, duplicate alias, alias equal to a field name, dotted source without `$as`	`INVALID_QUERY`	400
Source is not a timestamp field, sits inside a JSON column, or is not a dimension in strict mode	`INVALID_QUERY`	400
Source is `@db.encrypted`	`ENC_FIELD_AGG`	400
The adapter supports no calendar buckets at all	`BUCKET_NOT_SUPPORTED`	400
The adapter does not support the unit	`BUCKET_NOT_SUPPORTED`	400
The database cannot resolve the zone (MySQL time zone tables not loaded, or a zone newer than the server's tz data)	`BUCKET_TZ_UNAVAILABLE`	501

Programmatic calls throw DbError with these codes. The query rules and an unsupported unit report path: "$select" or "$groupBy" — for example Calendar bucket "week" is not supported by this adapter.

The field rules (which fields can be bucketed) report the field as path and give the same reason in both layers: programmatically Cannot bucket "points" — not a timestamp field (declare it number.timestamp), over HTTP Bucketing field "points" is not permitted — not a timestamp field (declare it number.timestamp). The other reasons are inside JSON-stored column "meta", adapter cannot filter on this storage type, not a dimension and adapter has no calendar buckets (the last one with BUCKET_NOT_SUPPORTED). Over HTTP an encrypted source gives the reason field is @db.encrypted (ciphertext cannot be compared or ordered), and a @db.writeOnly source is refused before any of these with Field "x" is @db.writeOnly and cannot be aggregated.

Changed in 0.1.133: programmatically, a strict-mode bucket source that is not a dimension used to throw Field "x" is not a dimension on path $groupBy, and an adapter without calendar buckets threw Calendar bucket "day" is not supported by this adapter on path $select. Over HTTP, a column that is neither a timestamp nor filterable by the adapter (a JSON or array column on SQL) used to report adapter cannot filter on this storage type; it now reports not a timestamp field.

BUCKET_TZ_UNAVAILABLE is a server configuration problem, not a bad request: the zone name was valid, but the database could not convert to it. No adapter falls back to UTC or returns null labels silently.

Adapter notes

All five adapters support all six units.

Adapter	Requirement
PostgreSQL	None. Independent of the session time zone. See PostgreSQL — Calendar buckets.
MySQL	Time zone tables loaded for any zone other than UTC; MySQL 8.0.28 or later (64-bit) for dates after 2038. See MySQL — Calendar buckets.
SQLite	A driver with the `registerFunction` hook — `BetterSqlite3Driver` has it. See SQLite — Calendar buckets.
MongoDB	MongoDB 4.0 or later. See MongoDB — Calendar buckets.
Memory	None — computed in process. See Memory — Grouped queries.

Each engine uses its own time zone data: PostgreSQL its tzdata, MySQL its mysql.time_zone* tables, MongoDB its bundled database, SQLite and memory the Node.js runtime's ICU data. If they are at different tzdata releases, labels near midnight can differ for dates affected by the changes between those releases. Keep the database's time zone data current.

Calendar Buckets ​

The bucket entry ​

URL form ​

The label ​

Hour buckets (since 0.1.147) ​

Time zones ​

Which fields can be bucketed ​

Discovering support through /meta ​

Filling gaps ​

Performance ​

Errors ​

Adapter notes ​

See also ​

Calendar Buckets

The bucket entry

URL form

The label

Hour buckets (since 0.1.147)

Time zones

Which fields can be bucketed

Discovering support through `/meta`

Filling gaps

Performance

Errors

Adapter notes

See also