# Time grains (/docs/datasets/time-grains)

When a dataset has a `timeKey`, pass a grain as `by` to aggregate by time period. Results include a `period` column.

<CodeBlock>
```typescript
await analytics.execute(Orders, {
  by: 'month',
  dimensions: ['country'],
  measures: ['revenue'],
});
```
</CodeBlock>

Supported grains:

- `minute`
- `hour`
- `day`
- `week`
- `month`
- `quarter`
- `year`

`minute` and `hour` buckets keep their time of day, so a period reads like `2024-01-01 10:00:00`.

## Timezone [#timezone]

Buckets and timezone-free time filters use the query timezone. Set an IANA
name on the client, or override it per query. The default is `UTC`; offsets
in timestamps always identify instants.

<CodeBlock>
```typescript
import { createDatasetClient } from '@hypequery/datasets';

const client = createDatasetClient({ queryBuilder: db, timezone: 'UTC' });
await client.execute(Orders, {
  by: 'day',
  timezone: 'America/New_York',
  measures: ['revenue'],
});
```
</CodeBlock>

The same `timezone` option works for windows and period comparisons.

## Fill missing periods [#fill-missing-periods]

A `by` query returns a row only for periods that have data. Set `fill: true` to get every period of the query's time range, as a chart or a per-period average needs:

<CodeBlock>
```typescript
await analytics.execute(Orders, {
  dimensions: ['country'],
  measures: ['orderCount', 'revenue'],
  by: 'day',
  fill: true,
  filters: [{ field: 'createdAt', operator: 'inDateRange', value: 'last 30 days' }],
});
```
</CodeBlock>

Every period in the range comes back for each dimension value seen in it. In an empty period, count measures are `0` and other measures are `null`, the same as a window measure's empty periods. `fill` needs `by` and exactly one bounded range on the time axis as top-level filters: one `between`, a lower and an upper bound, or one `inDateRange`. Without it the query fails. The number of periods counts against the row limit, and `having` can't be combined with `fill`.

## Restricting grains [#restricting-grains]

By default a dataset with a `timeKey` supports every grain. Pass `timeGrains` to narrow the list. Queries and the catalog's `supportedGrains` then use only those grains. This is how you refuse sub-day buckets when `timeKey` is a `Date` column with no time of day:

<CodeBlock>
```typescript
const DailySales = dataset('daily_sales', {
  source: 'daily_sales',
  timeKey: 'day',
  timeGrains: ['day', 'week', 'month', 'quarter', 'year'],
  dimensions: { day: dimension.timestamp() },
  measures: { revenue: measure.sum('amount') },
});
```
</CodeBlock>

## Time key and timestamp dimensions [#time-key-and-timestamp-dimensions]

`timeKey` names the physical timestamp column used for time bucketing. It does not create a selectable dimension by itself.

If you also want callers to select, filter, or group by that timestamp through the dataset API, add a timestamp dimension that maps a semantic name to the same column:

<CodeBlock>
```typescript
const Orders = dataset('orders', {
  source: 'orders',
  timeKey: 'created_at',
  dimensions: {
    createdAt: dimension.timestamp({ column: 'created_at' }),
    country: dimension.string(),
  },
  measures: {
    revenue: measure.sum('amount'),
  },
});
```
</CodeBlock>

With that setup, `.by('month')` buckets on `created_at`, while dataset queries can still use the semantic dimension name `createdAt`.

## Multiple time keys [#multiple-time-keys]

Many tables carry more than one timestamp: an order is created, shipped, and delivered. `timeKey` is the default axis. List the others in `timeKeys`, and a query picks one with `timeKey`:

<CodeBlock>
```typescript
const Orders = dataset('orders', {
  source: 'orders',
  timeKey: 'createdAt',
  timeKeys: ['shippedAt', 'delivered_at'],
  dimensions: {
    createdAt: dimension.timestamp({ column: 'created_at' }),
    shippedAt: dimension.timestamp({ column: 'shipped_at' }),
  },
  measures: { revenue: measure.sum('amount') },
});

// Revenue by the month orders shipped, not the month they were placed.
await analytics.execute(Orders, {
  measures: ['revenue'],
  by: 'month',
  timeKey: 'shippedAt',
});

// Metrics take the same option, including pinned grains.
const revenue = Orders.metric('revenue', { measure: 'revenue' });
await analytics.execute(revenue.by('week'), { timeKey: 'shippedAt' });
```
</CodeBlock>

Each entry is a dimension name or a column, like `timeKey`, and `timeKeys` requires a `timeKey`. A query that names no `timeKey`, or names the default, behaves exactly as before. In TypeScript, a query's `timeKey` only accepts the axes the dataset declares, so a typo fails to compile.

The selected axis drives everything time-based in the query:

- `by` buckets it into `period`.
- Window and period-over-period measures move along it, so their required bounded time range must be a filter on that axis.
- Timezone-free filters on **any** declared axis use the query timezone.

`timeGrains` applies to every axis. The catalog and contract list the alternates as `timeKeys`. Serve endpoints accept `timeKey` on datasets that declare alternates and reject any axis the dataset does not declare.

Deploying a dataset with `timeKeys` publishes them as contract 3 `alternateTimeFields`, and a deployed query selects one with `timeKey` (RFC 0029). Alternate time keys need the `queryBuilder` execution path. The MCP query tools and the Python SDK don't carry them yet.
