toStartOfMonth
Truncate a DateTime to the first day of the month for MoM reporting.
Signature
toStartOfMonth(datetime: DateTime): DateReturns
Date
What it does
Rounds a DateTime or Date to the first calendar day of its month. Returns a Date. Used for month-over-month comparisons and monthly cohort analysis.
toStartOfMonth is the go-to function for monthly time-series aggregations: MAU, MoM revenue growth, monthly retention. It normalises any date within a month to the 1st, so GROUP BY toStartOfMonth(ts) produces exactly one row per calendar month.
Notes
- Returns a Date (year-month-01), not a DateTime.
- For fiscal months that don't align with calendar months, use toStartOfInterval with a INTERVAL expression.
- toRelativeMonthNum is useful when you need an integer month index for array operations.
Example SQL
toStartOfMonth in ClickHouse SQL
TypeScript with hypequery
Use toStartOfMonth 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 toStartOfMonth when you need them inside a builder query.
Common questions
What developers search for with toStartOfMonth
Monthly active users ClickHouse query
GROUP BY toStartOfMonth(event_time), COUNT(DISTINCT user_id) gives you MAU per month. Add a WHERE clause to limit to the last 12 months.
Month-over-month growth ClickHouse
Self-join on toStartOfMonth offset by 1 month, or use lagInFrame in a window function over the monthly aggregation.
FAQ
Frequently asked questions about toStartOfMonth
What does toStartOfMonth return?
A Date value set to the first day of the month (e.g. 2024-03-01) in the column's time zone.
Related functions
Functions used alongside toStartOfMonth
Related guides
Next step
Use toStartOfMonth in a type-safe TypeScript query
hypequery generates TypeScript types from your ClickHouse schema. Use toStartOfMonth alongside the builder, and reach for raw SQL expressions when the function is not exposed as a dedicated helper.