toStartOfWeek
Round a DateTime to the start of the week — weekly cohort and trend analysis.
Signature
toStartOfWeek(datetime: DateTime, mode?: UInt8): DateReturns
Date
What it does
Rounds a DateTime or Date value down to the start of the calendar week. Mode 0 (default) starts on Monday; mode 1 starts on Sunday.
toStartOfWeek is used for weekly reporting: weekly active users (WAU), week-over-week growth, and cohort retention by week. The mode argument controls whether the week starts on Monday (ISO 8601, mode 0) or Sunday (US convention, mode 1). Returns a Date, not DateTime.
Notes
- Returns a Date, not a DateTime — no time component.
- Mode 0 = Monday start (ISO), Mode 1 = Sunday start (US/Google Analytics convention).
- For retention cohorts, use toStartOfWeek on both signup_date and event_date, then compare.
Example SQL
toStartOfWeek in ClickHouse SQL
TypeScript with hypequery
Use toStartOfWeek 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 toStartOfWeek when you need them inside a builder query.
Common questions
What developers search for with toStartOfWeek
Weekly active users in ClickHouse
WAU is toStartOfWeek + COUNT(DISTINCT user_id). The mode argument lets you match Monday or Sunday week starts to align with your BI tool.
Week-over-week growth ClickHouse
Calculate WoW by joining the same table on toStartOfWeek offset by 7 days, or use a window function with lagInFrame over the weekly time series.
FAQ
Frequently asked questions about toStartOfWeek
Does toStartOfWeek return a Date or DateTime?
It returns a Date (no time component). If you need a DateTime for joins, cast it: toDateTime(toStartOfWeek(ts)).
How do I start the week on Sunday instead of Monday?
Pass mode=1 as the second argument: toStartOfWeek(created_at, 1).
Related functions
Functions used alongside toStartOfWeek
Next step
Use toStartOfWeek in a type-safe TypeScript query
hypequery generates TypeScript types from your ClickHouse schema. Use toStartOfWeek alongside the builder, and reach for raw SQL expressions when the function is not exposed as a dedicated helper.