How to TRUNCATE DATE in ClickHouse

How do I truncate a DateTime to day, week, or month in ClickHouse?

date_trunc() shortens a Date or DateTime value to the start of a specified time unit (minute, hour, day, week, month, quarter, or year).

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why use date_trunc() in ClickHouse?

Aggregate queries often need timestamps aligned to consistent boundaries. date_trunc() snaps any Date/DateTime to the opening tick of a unit so group by clauses work reliably.

What is the basic syntax?

date_trunc('unit', timestamp_expression [, 'timezone']) → DateTime/Date.
Supported units: second, minute, hour, day, week, month, quarter, year.

How do I truncate to day for daily sales?

Use date_trunc('day', order_date) so all orders on the same calendar day share one key.

How do I handle time zones?

Add the optional third parameter: date_trunc('hour', created_at, 'UTC'). This prevents double-counting when users span zones.

Can I truncate to custom intervals?

For 15-minute buckets, combine toStartOfInterval: toStartOfInterval(order_date, INTERVAL 15 minute). It returns the nearest lower 15-minute mark.

How to group order totals by month?

GROUP BY date_trunc('month', order_date) aggregates all same-month rows, even across years.

Best practices

Store timestamps in UTC, truncate in UTC, then convert for display. Index the raw DateTime column; the function will still leverage partition pruning if partitions are also UTC-aligned.

Common pitfalls

Truncating before applying filters can broaden the time range. Always filter first, then group. Avoid string-to-date casts inside date_trunc(); cast once in a CTE.

Why How to TRUNCATE DATE in ClickHouse is important

How to TRUNCATE DATE in ClickHouse Example Usage


-- Monthly revenue per customer in UTC
SELECT 
    customer_id,
    date_trunc('month', order_date, 'UTC') AS order_month,
    sum(total_amount) AS revenue
FROM Orders
GROUP BY customer_id, order_month
ORDER BY customer_id, order_month;

How to TRUNCATE DATE in ClickHouse Syntax


date_trunc('unit', timestamp_expression [, 'timezone'])
-- unit: 'second' | 'minute' | 'hour' | 'day' | 'week' | 'month' | 'quarter' | 'year'
-- timestamp_expression: Date or DateTime column or expression
-- timezone: optional IANA name

-- Example using ecommerce tables
SELECT 
    date_trunc('day', order_date) AS order_day,
    sum(total_amount) AS daily_sales
FROM Orders
GROUP BY order_day
ORDER BY order_day;

Common Mistakes

Mistake: Truncating before filtering. Why wrong: Broadens dataset and slows query. Fix: Apply WHERE conditions first, then date_trunc() in SELECT or GROUP BY.
Mistake: Mixing local time storage with UTC truncation. Why wrong: Off-by-hours errors around DST. Fix: Store in UTC and truncate in UTC, convert only for display.