toStartOfInterval
Bucket by any arbitrary interval — 5-minute, hourly, bi-weekly, or custom.
Signature
toStartOfInterval(datetime: DateTime, interval: Interval, [origin: DateTime]): DateTimeReturns
DateTime
What it does
Rounds a DateTime to the start of the nearest interval boundary. Supports INTERVAL n SECOND/MINUTE/HOUR/DAY/WEEK/MONTH/QUARTER/YEAR. The origin parameter controls where intervals are anchored.
toStartOfInterval is the most flexible date-bucketing function in ClickHouse. It handles any granularity that the specialised functions (toStartOfDay, toStartOfWeek…) cannot. Common uses: 5-minute candles for time-series charts, 15-minute activity heatmaps, bi-weekly billing cycles. The optional origin parameter anchors intervals at a specific timestamp rather than the Unix epoch.
Notes
- Intervals smaller than 1 second are not supported.
- The origin parameter (third argument) defaults to the Unix epoch (1970-01-01 00:00:00 UTC).
- For week intervals, origin controls which day of the week is considered the "start".
- More flexible than toStartOfDay/Week/Month but slightly slower on very large datasets.
Example SQL
toStartOfInterval in ClickHouse SQL
TypeScript with hypequery
Use toStartOfInterval in a typed TypeScript query
hypequery gives you a type-safe query builder for ClickHouse. The generated schema maps your ClickHouse columns to TypeScript types, and raw SQL expressions let you incorporate functions like toStartOfInterval when you need them inside a builder query.
Common questions
What developers search for with toStartOfInterval
ClickHouse 5-minute buckets
toStartOfInterval(ts, INTERVAL 5 MINUTE) groups events into 5-minute windows. Ideal for real-time monitoring dashboards and rate charts.
Custom time interval grouping ClickHouse
When toStartOfDay and toStartOfHour are too coarse, toStartOfInterval handles any granularity. Pass the interval as a literal INTERVAL n UNIT expression.
FAQ
Frequently asked questions about toStartOfInterval
What intervals does toStartOfInterval support?
SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, and YEAR. Sub-second intervals are not supported.
How do I anchor a weekly interval to Saturday instead of Monday?
Set origin to any Saturday date: toStartOfInterval(ts, INTERVAL 1 WEEK, toDateTime('2024-01-06')).
Next step
Use toStartOfInterval in a type-safe TypeScript query
hypequery generates TypeScript types from your ClickHouse schema. Use toStartOfInterval alongside the builder, and reach for raw SQL expressions when the function is not exposed as a dedicated helper.