ClickHouse toStartOfInterval with GROUP BY in TypeScript

toStartOfInterval is the most flexible date bucketing function in ClickHouse. Instead of reaching for a separate function for each granularity — toStartOfHour, toStartOfDay, toStartOfWeek — you can use a single function with an INTERVAL expression to bucket a DateTime into any interval you need.

What toStartOfInterval Does

toStartOfInterval(datetime, INTERVAL n unit) rounds a DateTime down to the start of the containing interval. The result is the floor of the timestamp, not a midpoint or ceiling.

Supported interval units: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR. This makes it more flexible than the fixed-granularity helpers like toStartOfHour or toStartOfDay, which only do one thing.

Basic GROUP BY with an Alias

Here is a time-series query that counts events per hour:

ClickHouse allows GROUP BY bucket where bucket is an alias defined in the SELECT clause. This is one area where ClickHouse differs from strict SQL dialects — standard SQL does not allow grouping by a SELECT-level alias, but ClickHouse does.

The key constraint: the alias must be defined in the same SELECT clause. You cannot reference an alias from a subquery's SELECT in an outer GROUP BY, and you cannot invent aliases that aren't in SELECT. This query fails:

The correct pattern is to define the alias in SELECT and then reference it in GROUP BY, as shown in the working example above.

What toStartOfInterval Returns

toStartOfInterval returns a DateTime when the input is DateTime, and a Date when the input is Date. In both cases ClickHouse returns this as a string to the client — not a JavaScript Date object.

A DateTime result looks like "2026-04-25 14:00:00". A Date result looks like "2026-04-25". There is no automatic coercion to a JS Date — you parse it yourself if needed:

This is consistent with how hypequery maps ClickHouse types: DateTime columns in the schema are typed as string, Date columns as string. The value coming back from toStartOfInterval is the same shape.

Using toStartOfInterval in hypequery

hypequery's .select() accepts raw SQL expressions as strings. This is the straightforward way to use toStartOfInterval:

The schema-generated type for created_at is string (since ClickHouse DateTime maps to string), which is consistent with what toStartOfInterval hands back. No type mismatch to work around.

A Practical Dashboard Query

Real dashboards usually need a variable granularity — hourly for short ranges, daily for longer ones. A common pattern:

If you later decide this query specifically needs an explicit ClickHouse PREWHERE, treat that as a raw SQL optimisation step rather than assuming there is a dedicated builder helper for it today. See PREWHERE vs WHERE for when that tradeoff matters.

Filling Gaps

One limitation of this query: if there are no events in a time bucket, that bucket won't appear in the results. ClickHouse has a WITH FILL clause for ORDER BY that can generate missing intervals:

This fills in zero-count buckets automatically. It's a raw SQL feature — use it via an escape hatch if your dashboard needs gap filling at the database level.

---

Related: ClickHouse Time-Series Patterns · ClickHouse TypeScript Guide · PREWHERE vs WHERE