Date rounding in ClickHouse transforms messy timestamps into clean time periods for grouping and analysis. Whether you're building monthly revenue reports or weekly user cohorts, ClickHouse's toStartOf* functions handle the complexity of truncating dates to meaningful boundaries.
This guide covers all five date rounding functions, from basic year truncation to handling ISO weeks and time zones, plus practical examples for exposing your rounded data through REST APIs.
Quick function reference
ClickHouse gives you five toStartOf* functions that round dates down to the beginning of time periods. Each function takes a Date or DateTime value and returns the truncated result, which makes time-based groupings straightforward.
| Function | Rounds To | Example Input | Example Output |
|---|---|---|---|
toStartOfYear() | January 1st | 2024-09-15 | 2024-01-01 |
toStartOfISOYear() | First day of ISO year | 2024-01-01 | 2024-01-01 |
toStartOfQuarter() | Quarter start (Jan, Apr, Jul, Oct) | 2024-09-15 | 2024-07-01 |
toStartOfMonth() | First day of month | 2024-09-15 | 2024-09-01 |
toStartOfWeek() | Monday or Sunday | 2024-09-15 | 2024-09-09 |
toStartOfYear
SELECT toStartOfYear('2024-09-15') AS year_start;
-- Returns: 2024-01-01
This function always returns January 1st of the given year, regardless of the input date.
toStartOfISOYear
SELECT toStartOfISOYear('2024-01-01') AS iso_year_start;
-- Returns: 2024-01-01
ISO years follow the ISO 8601 standard where the first week contains at least four days of the new year. This can cause the ISO year to start in December of the previous calendar year.
toStartOfQuarter
SELECT toStartOfQuarter('2024-09-15') AS quarter_start;
-- Returns: 2024-07-01 (Q3 start)
Quarters begin on January 1st, April 1st, July 1st, and October 1st.
toStartOfMonth
SELECT toStartOfMonth('2024-09-15') AS month_start;
-- Returns: 2024-09-01
Always returns the first day of the month, preserving the year and month components.
toStartOfWeek
SELECT toStartOfWeek('2024-09-15', 0) AS week_start_sunday;
-- Returns: 2024-09-15 (Sunday)
SELECT toStartOfWeek('2024-09-15', 1) AS week_start_monday;
-- Returns: 2024-09-09 (Monday)
The second parameter controls the week start: 0 for Sunday, 1 for Monday (default).
Calendar year truncation with toStartOfYear
The toStartOfYear function rounds any date down to January 1st of the same year. It works with both Date and DateTime types, keeping only the year component while setting month and day to 1.
Syntax and return type
toStartOfYear(date_or_datetime)
The function accepts Date, Date32, DateTime, or DateTime64 values and returns a Date type. Time zones don't affect the result since we're only working with the date portion.
Basic example
SELECT
original_date,
toStartOfYear(original_date) AS year_start
FROM VALUES(
('2024-01-01'),
('2024-06-15'),
('2024-12-31')
) AS t(original_date);
This query shows how different dates within 2024 all round down to the same year start.
Common edge cases
Leap years don't affect toStartOfYear behavior since February 29th rounds to January 1st like any other date. DateTime inputs lose their time component entirely—2024-09-15 14:30:00 becomes 2024-01-01.
Time zone conversions happen before rounding, so converting a DateTime to a different time zone might change which year the function returns if the conversion crosses a year boundary.
ISO year truncation with toStartOfISOYear
ISO years follow the ISO 8601 week-numbering system where the first week of the year contains at least four days from the new year. This means some ISO years start on December 29th, 30th, or 31st of the previous calendar year.
The key difference appears when January 1st falls on a Friday, Saturday, or Sunday. In those cases, the ISO year actually starts in December of the previous calendar year.
ISO week 53 considerations
SELECT
calendar_date,
toStartOfYear(calendar_date) AS calendar_year,
toStartOfISOYear(calendar_date) AS iso_year
FROM VALUES(
'2021-01-01', -- Friday
'2022-01-01', -- Saturday
'2023-01-01' -- Sunday
) AS t(calendar_date);
When January 1st falls on Friday through Sunday, the ISO year begins in the previous December. This affects year-over-year comparisons and financial reporting that relies on ISO standards.
Aggregation example
SELECT
toStartOfISOYear(order_date) AS iso_year,
COUNT(*) AS orders,
SUM(revenue) AS total_revenue
FROM sales_data
WHERE order_date >= '2023-01-01'
GROUP BY iso_year
ORDER BY iso_year;
This query groups sales data by ISO year, which might be more appropriate for businesses that follow ISO week numbering for reporting.
Quarter truncation with toStartOfQuarter
Business quarters provide a natural way to group financial and operational data into three-month periods. The toStartOfQuarter function maps any date to the start of its respective quarter.
Syntax
toStartOfQuarter(date_or_datetime)
The function returns a Date type representing the first day of the quarter: January 1st (Q1), April 1st (Q2), July 1st (Q3), or October 1st (Q4).
Financial reporting use case
SELECT
toStartOfQuarter(transaction_date) AS quarter,
SUM(amount) AS quarterly_revenue,
COUNT(*) AS transaction_count
FROM financial_transactions
WHERE transaction_date >= '2024-01-01'
GROUP BY quarter
ORDER BY quarter;
This pattern works well for quarterly business reviews, board reports, and any analysis that aligns with standard business quarters.
Many companies use fiscal years that don't align with calendar years. You'll need custom logic to handle fiscal quarters that start in months other than January.
Month truncation with toStartOfMonth
Monthly aggregations are among the most common time-based groupings in analytics. The toStartOfMonth function simplifies creating month-over-month comparisons and trend analysis.
Syntax
toStartOfMonth(date_or_datetime)
Returns a Date representing the first day of the month, keeping the year and month while setting the day to 1.
Monthly cohort analysis
SELECT
toStartOfMonth(registration_date) AS cohort_month,
COUNT(DISTINCT user_id) AS new_users,
COUNT(DISTINCT CASE
WHEN last_login_date >= cohort_month + INTERVAL 30 DAY
THEN user_id
END) AS retained_users
FROM user_activity
WHERE registration_date >= '2024-01-01'
GROUP BY cohort_month
ORDER BY cohort_month;
This query creates monthly user cohorts and calculates basic retention metrics by grouping users by their registration month.
Week truncation with toStartOfWeek
Weekly analysis requires choosing whether weeks start on Sunday or Monday, depending on your business context and regional conventions. The toStartOfWeek function accommodates both approaches.
Choosing Monday or Sunday
toStartOfWeek(date_or_datetime, mode)
The mode parameter determines the week start:
- Mode 0: Week starts on Sunday (US convention)
- Mode 1: Week starts on Monday (ISO 8601 standard, European convention)
Most business analytics use Monday as the week start since it aligns with typical work weeks, while consumer-facing applications might prefer Sunday to match calendar conventions.
Weekly trend query
SELECT
toStartOfWeek(event_date, 1) AS week_start,
COUNT(DISTINCT user_id) AS weekly_active_users,
COUNT(*) AS total_events
FROM user_events
WHERE event_date >= today() - INTERVAL 8 WEEK
GROUP BY week_start
ORDER BY week_start;
This query tracks weekly active users over the past eight weeks, using Monday as the week start for business reporting consistency.
Handling time zones and week numbering
Time zones can significantly impact date rounding results, especially for DateTime values near day boundaries. Converting to the appropriate time zone before rounding ensures accurate groupings for global applications.
Setting the session time zone
SET timezone = 'America/New_York';
SELECT
event_timestamp,
toStartOfMonth(event_timestamp) AS month_start
FROM events
WHERE event_timestamp >= '2024-09-01 00:00:00';
Setting the session timezone affects how DateTime values are interpreted. An event at midnight UTC might fall into different months depending on the target timezone.
Converting before rounding
SELECT
event_timestamp,
toTimeZone(event_timestamp, 'Europe/London') AS london_time,
toStartOfWeek(toTimeZone(event_timestamp, 'Europe/London'), 1) AS london_week
FROM events
WHERE event_timestamp >= '2024-09-01';
Use toTimeZone to convert DateTime values to the appropriate timezone before applying rounding functions. This approach gives you explicit control over the conversion process rather than relying on session settings.
Grouping and filling gaps after rounding
Time series data often has gaps—periods with no events or measurements. ClickHouse's WITH FILL clause creates continuous time series by filling missing periods with default values.
GROUP BY with FILL
SELECT
toStartOfMonth(order_date) AS month,
COUNT(*) AS order_count
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY month
ORDER BY month WITH FILL
FROM toDate('2024-01-01')
TO toDate('2024-12-01')
STEP INTERVAL 1 MONTH;
The WITH FILL clause ensures every month appears in the results, even months with zero orders. This prevents gaps in charts and makes trend analysis more reliable.
Gap-free time series
SELECT
week_start,
COALESCE(weekly_revenue, 0) AS revenue
FROM (
SELECT
toStartOfWeek(sale_date, 1) AS week_start,
SUM(amount) AS weekly_revenue
FROM sales
WHERE sale_date >= today() - INTERVAL 12 WEEK
GROUP BY week_start
ORDER BY week_start WITH FILL
FROM toMonday(today() - INTERVAL 12 WEEK)
TO toMonday(today())
STEP INTERVAL 1 WEEK
);
This pattern combines date rounding with gap filling to create a complete 12-week revenue time series, replacing NULL values with zeros using COALESCE.
When to pre-compute in a materialized view
Frequently accessed date-rounded aggregations benefit from pre-computation in materialized views. Instead of calculating toStartOfMonth(order_date) on every query, you can store the rounded date alongside your data. For a comprehensive guide on what materialized views are and why they matter for real-time analytics, this approach can significantly improve query performance.
Performance benchmarks
Pre-computing rounded dates eliminates the function call overhead and enables more efficient indexing. Queries that group by pre-computed rounded dates typically run faster than those computing the rounding on-demand, especially for large datasets.
The performance gain increases with data volume since the rounding calculation scales linearly with row count, while pre-computed values only require index lookups.
Maintenance trade-offs
Materialized views require additional storage for the pre-computed columns and slightly slower insert performance. However, the query speedup usually justifies the costs for frequently accessed aggregations.
Consider pre-computation when you regularly group by the same time periods across multiple queries, or when query latency requirements are strict enough that function call overhead matters.
Building ClickHouse-based business intelligence APIs with Tinybird
Transform your date rounding queries into comprehensive business intelligence dashboards using Tinybird's managed ClickHouse platform. This approach enables real-time KPI tracking, financial reporting, and operational analytics across multiple time dimensions.
Instead of managing ClickHouse infrastructure, you define your BI analytics logic as code and Tinybird handles the operational complexity. The platform provides sub-second query performance for complex time-based aggregations while abstracting away database administration.
1. Create a data source for business metrics
Define your business intelligence data structure in a data source file:
SCHEMA >
`timestamp` DateTime64(3, 'UTC') `json:$.timestamp`,
`company_id` String `json:$.company_id`,
`department` LowCardinality(String) `json:$.department`,
`revenue` Decimal(15, 2) `json:$.revenue`,
`orders` UInt32 `json:$.orders`,
`customers` UInt32 `json:$.customers`,
`region` LowCardinality(String) `json:$.region`,
`product_category` LowCardinality(String) `json:$.product_category`,
`sales_rep_id` String `json:$.sales_rep_id`,
`deal_size` Decimal(10, 2) `json:$.deal_size`,
`conversion_rate` Float32 `json:$.conversion_rate`,
`churn_risk_score` Float32 `json:$.churn_risk_score`
ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(timestamp)"
ENGINE_SORTING_KEY "timestamp, company_id"
This schema captures comprehensive business metrics including revenue, orders, customer data, and operational KPIs. The partitioning by month ensures fast queries as your business analytics data grows.
2. Ingest example data using the Events API
Add sample business metrics to test your analytics using the Events API. First, start your local Tinybird development environment:
# Start Tinybird locally
tb dev
Then ingest sample data to your local instance:
# Ingest sample business metrics to localhost
curl -X POST "http://localhost:7181/v0/events" \
-H "Authorization: Bearer $TINYBIRD_TOKEN" \
-H "Content-Type: application/json" \
-d '[
{
"timestamp": "2024-01-15T10:30:00.000Z",
"company_id": "acme_corp",
"department": "Sales",
"revenue": 125000.50,
"orders": 45,
"customers": 23,
"region": "North America",
"product_category": "Enterprise",
"sales_rep_id": "rep_001",
"deal_size": 5500.00,
"conversion_rate": 0.15,
"churn_risk_score": 0.2
},
{
"timestamp": "2024-01-15T11:00:00.000Z",
"company_id": "tech_startup",
"department": "Marketing",
"revenue": 75000.25,
"orders": 28,
"customers": 18,
"region": "Europe",
"product_category": "SMB",
"sales_rep_id": "rep_002",
"deal_size": 2800.00,
"conversion_rate": 0.22,
"churn_risk_score": 0.1
},
{
"timestamp": "2024-01-15T12:00:00.000Z",
"company_id": "enterprise_ltd",
"department": "Sales",
"revenue": 200000.00,
"orders": 12,
"customers": 8,
"region": "Asia Pacific",
"product_category": "Enterprise",
"sales_rep_id": "rep_003",
"deal_size": 25000.00,
"conversion_rate": 0.08,
"churn_risk_score": 0.05
}
]'
This sample data includes various business metrics across different departments, regions, and product categories to test your analytics pipes.
3. Build business intelligence pipes
Create pipes that analyze business performance across multiple time dimensions:
NODE business_metrics
SQL >
%
SELECT
timestamp,
company_id,
department,
revenue,
orders,
customers,
region,
product_category,
sales_rep_id,
deal_size,
conversion_rate,
churn_risk_score,
toStartOfMonth(timestamp) AS month_start,
toStartOfQuarter(timestamp) AS quarter_start,
toStartOfWeek(timestamp, 1) AS week_start
FROM business_metrics
WHERE timestamp >= {{DateTime(start_date, '2024-01-01 00:00:00')}}
AND timestamp <= {{DateTime(end_date, '2024-12-31 23:59:59')}}
{\% if defined(company_filter) %}
AND company_id = {{String(company_filter)}}
{\% end %}
{\% if defined(department_filter) %}
AND department = {{String(department_filter)}}
{\% end %}
{\% if defined(region_filter) %}
AND region = {{String(region_filter)}}
{\% end %}
NODE monthly_kpi_dashboard
SQL >
%
SELECT
month_start,
department,
region,
count() AS total_records,
sum(revenue) AS total_revenue,
sum(orders) AS total_orders,
sum(customers) AS total_customers,
round(avg(deal_size), 2) AS avg_deal_size,
round(avg(conversion_rate) * 100, 2) AS avg_conversion_rate_percent,
round(avg(churn_risk_score) * 100, 2) AS avg_churn_risk_percent,
-- Growth calculations
round((sum(revenue) - lag(sum(revenue)) OVER (PARTITION BY department, region ORDER BY month_start)) * 100.0 / lag(sum(revenue)) OVER (PARTITION BY department, region ORDER BY month_start), 2) AS revenue_growth_percent,
-- Top performing sales reps
topK(3)(sales_rep_id) AS top_sales_reps,
-- Revenue distribution by product category
topK(3)(product_category) AS top_product_categories
FROM business_metrics
GROUP BY month_start, department, region
ORDER BY month_start DESC, total_revenue DESC
{\% if defined(limit) %}
LIMIT {{Int32(limit, 50)}}
{\% end %}
TYPE ENDPOINT
Create a quarterly business review pipe:
NODE quarterly_business_review
SQL >
%
SELECT
quarter_start,
department,
count() AS total_records,
sum(revenue) AS quarterly_revenue,
sum(orders) AS quarterly_orders,
sum(customers) AS quarterly_customers,
round(avg(deal_size), 2) AS avg_deal_size,
round(avg(conversion_rate) * 100, 2) AS avg_conversion_rate_percent,
round(avg(churn_risk_score) * 100, 2) AS avg_churn_risk_percent,
-- Quarter-over-quarter growth
round((sum(revenue) - lag(sum(revenue)) OVER (PARTITION BY department ORDER BY quarter_start)) * 100.0 / lag(sum(revenue)) OVER (PARTITION BY department ORDER BY quarter_start), 2) AS qoq_revenue_growth_percent,
-- Performance indicators
CASE
WHEN avg(conversion_rate) > 0.15 AND avg(churn_risk_score) < 0.1 THEN 'excellent'
WHEN avg(conversion_rate) > 0.10 AND avg(churn_risk_score) < 0.2 THEN 'good'
WHEN avg(conversion_rate) > 0.05 AND avg(churn_risk_score) < 0.3 THEN 'fair'
ELSE 'needs_improvement'
END AS performance_rating
FROM business_metrics
GROUP BY quarter_start, department
ORDER BY quarter_start DESC, quarterly_revenue DESC
{\% if defined(limit) %}
LIMIT {{Int32(limit, 20)}}
{\% end %}
TYPE ENDPOINT
4. Test locally and deploy to cloud
Test your business intelligence APIs locally first:
# Test monthly KPI dashboard locally
curl "http://localhost:7181/v0/pipes/monthly_kpi_dashboard.json?start_date=2024-01-01&end_date=2024-01-31&limit=10" \
-H "Authorization: Bearer $TINYBIRD_TOKEN"
Example local API response:
{
"meta": [
{
"name": "month_start",
"type": "Date"
},
{
"name": "department",
"type": "String"
},
{
"name": "region",
"type": "String"
},
{
"name": "total_records",
"type": "UInt64"
},
{
"name": "total_revenue",
"type": "Decimal(15,2)"
},
{
"name": "total_orders",
"type": "UInt64"
},
{
"name": "total_customers",
"type": "UInt64"
},
{
"name": "avg_deal_size",
"type": "Float64"
},
{
"name": "avg_conversion_rate_percent",
"type": "Float64"
},
{
"name": "avg_churn_risk_percent",
"type": "Float64"
},
{
"name": "revenue_growth_percent",
"type": "Float64"
},
{
"name": "top_sales_reps",
"type": "Array(String)"
},
{
"name": "top_product_categories",
"type": "Array(String)"
}
],
"data": [
{
"month_start": "2024-01-01",
"department": "Sales",
"region": "North America",
"total_records": 1,
"total_revenue": 125000.50,
"total_orders": 45,
"total_customers": 23,
"avg_deal_size": 5500.00,
"avg_conversion_rate_percent": 15.0,
"avg_churn_risk_percent": 20.0,
"revenue_growth_percent": null,
"top_sales_reps": ["rep_001"],
"top_product_categories": ["Enterprise"]
}
],
"rows": 1,
"statistics": {
"elapsed": 0.000856,
"rows_read": 3,
"bytes_read": 512
}
}
Once you're satisfied with your local testing, deploy to Tinybird's cloud infrastructure:
# Deploy to cloud
tb --cloud deploy
Test your production APIs:
# Monthly KPI dashboard
curl "https://api.tinybird.co/v0/pipes/monthly_kpi_dashboard.json?start_date=2024-01-01&end_date=2024-06-30&department_filter=Sales&limit=20" \
-H "Authorization: Bearer $TINYBIRD_TOKEN"
# Quarterly business review
curl "https://api.tinybird.co/v0/pipes/quarterly_business_review.json?start_date=2024-01-01&end_date=2024-12-31&company_filter=acme_corp" \
-H "Authorization: Bearer $TINYBIRD_TOKEN"
The APIs return comprehensive business intelligence including revenue trends, conversion rates, growth metrics, and performance ratings. This enables real-time insights into business performance across multiple time dimensions, department analysis, and regional comparisons for data-driven decision making.
Ship faster with Tinybird's managed ClickHouse
Managing ClickHouse infrastructure requires engineering resources for tasks such as handling replicas, backups, monitoring, and scaling. Using a managed ClickHouse service automates these operational aspects and provides access to the same date functions and query performance as self-hosted ClickHouse.
Sign up for a free Tinybird plan
Tinybird is an analytics backend and platform for building real-time analytics APIs, offering managed ClickHouse as a core component along with built-in API generation, real-time ingestion, and developer tooling.
FAQs about rounding dates in ClickHouse
Why does toStartOfWeek return a different day than expected?
The toStartOfWeek function uses a mode parameter that defaults to Monday (mode 1). If you expect Sunday as the week start, explicitly pass mode 0: toStartOfWeek(date, 0). Different regions and business contexts use different week start conventions.
How do I round Unix timestamps to date periods?
Convert Unix timestamps to DateTime first using toDateTime(timestamp), then apply any date rounding function. For example: toStartOfMonth(toDateTime(unix_timestamp)) rounds a Unix timestamp to the start of its month.
Can I round dates to custom periods like fiscal years?
ClickHouse doesn't provide built-in fiscal year functions, but you can calculate them using date arithmetic. For a fiscal year starting in July, use: toStartOfMonth(date - INTERVAL 6 MONTH) + INTERVAL 6 MONTH to shift the calendar year calculation.