toStartOfDay
Truncate a DateTime to midnight — the foundation of daily analytics bucketing.
Signature
toStartOfDay(datetime: DateTime): DateTimeReturns
DateTime
What it does
Rounds a DateTime value down to midnight (00:00:00) on the same calendar day in the column's time zone. Essential for GROUP BY day queries.
toStartOfDay is the most commonly used date-truncation function in ClickHouse analytics. It strips the time component, leaving the date at midnight, so that events from the same calendar day hash to the same bucket. Combined with GROUP BY, it produces one row per day — the building block of trend charts, daily active user counts, and rolling-window calculations.
Notes
- The result is in the time zone of the DateTime column — use toStartOfDay(col, 'UTC') to force UTC.
- For daily partitioning use toYYYYMMDD instead — it returns a UInt32 suitable for PARTITION BY.
- Combine with toStartOfWeek and toStartOfMonth for drill-down hierarchies.
Example SQL
toStartOfDay in ClickHouse SQL
TypeScript with hypequery
Use toStartOfDay 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 toStartOfDay when you need them inside a builder query.
Common questions
What developers search for with toStartOfDay
GROUP BY day in ClickHouse
toStartOfDay is the standard way to bucket rows into calendar days. It normalises any DateTime to midnight, so GROUP BY toStartOfDay(ts) gives one row per day.
Daily active users (DAU) in ClickHouse
DAU queries count distinct user_ids per day. Use toStartOfDay on your event timestamp column, then COUNT(DISTINCT user_id) within each day bucket.
ClickHouse date truncation TypeScript
hypequery exposes raw SQL expressions inside select and groupBy, so you can pass toStartOfDay(created_at) directly and get typed results back.
FAQ
Frequently asked questions about toStartOfDay
What does toStartOfDay return?
It returns a DateTime value set to 00:00:00 on the same calendar day as the input, in the column's time zone.
How do I use toStartOfDay with a specific time zone?
Pass the time zone as a second argument: toStartOfDay(created_at, 'America/New_York'). Without it, the column's stored time zone is used.
Can I use toStartOfDay in a WHERE clause?
Yes, but it's more efficient to filter on a range: WHERE created_at >= toStartOfDay(today()) instead of WHERE toStartOfDay(created_at) = today().
Related functions
Functions used alongside toStartOfDay
Next step
Use toStartOfDay in a type-safe TypeScript query
hypequery generates TypeScript types from your ClickHouse schema. Use toStartOfDay alongside the builder, and reach for raw SQL expressions when the function is not exposed as a dedicated helper.