How to Use dateDiff in ClickHouse

What does dateDiff do in ClickHouse?

dateDiff subtracts a start date from an end date and returns the integer count of the requested time unit (second, minute, hour, day, week, month, quarter, or year). It works with Date, Date32, and DateTime types.

Why would I use dateDiff in ecommerce analytics?

Use dateDiff to measure customer latency—days between signup and first order, hours between order and shipping, or months between repeat purchases.These metrics uncover churn risk and growth opportunities.

How do I write the dateDiff syntax?

Place the unit first, then the two date expressions: dateDiff('unit', start_date, end_date). ClickHouse returns a signed integer; negative results mean the end date precedes the start.

Can I mix Date and DateTime?

Yes.ClickHouse casts Date to midnight of that day when mixed with DateTime, letting you compare at any granularity.

Example – days between customer signup and first order

The query below joins Customers with their earliest Orders and calculates the days waited before purchase.

SELECT c.id,
c.name,
dateDiff('day', c.created_at, MIN(o.order_date)) AS days_to_first_order
FROM Customers AS c
JOIN Orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name;


What units are supported?

"second", "minute", "hour", "day", "week", "month", "quarter", and "year" are valid.Capitalization is ignored.

Best practices for dateDiff

Always index or use partition keys on the date columns you filter. Use consistent time zones—store in UTC and convert in the final SELECT to avoid off-by-one errors around DST changes.

Common mistakes and fixes

Passing unit as identifier

Wrong: dateDiff(day, …). The unit must be a single-quoted string. Fix: dateDiff('day', …).

Reversing start and end dates

ClickHouse returns negative values if end_date < start_date.Swap arguments or wrap the result in abs() when you only need magnitude.

Related ClickHouse date functions

Complement dateDiff with dateAdd for cohort tracking, toStartOfWeek for grouping, and date_trunc for bucketing time series.

.