dateDiff
Count calendar-boundary crossings between two dates — not elapsed time, which is the gotcha everyone hits.
Signature
dateDiff(unit: String, startdate: DateTime, enddate: DateTime[, timezone: String]): Int64Returns
Int64
What it does
Returns the number of `unit` boundaries (day, month, year, etc.) crossed between two dates. It counts calendar boundaries, not elapsed time — so 23:59 on Dec 31 to 00:01 on Jan 1 is 1 day.
dateDiff is the function people reach for to answer "how many days/months/years between these two timestamps?" — but its behavior surprises almost everyone the first time. It does not measure elapsed duration. It counts how many `unit` boundaries you cross going from the start to the end. dateDiff('day', '2024-12-31 23:59:00', '2025-01-01 00:01:00') returns 1, even though only two minutes elapsed, because a single midnight boundary was crossed. Likewise dateDiff('year', '2024-12-31', '2025-01-01') is 1. This is exactly what you want for questions phrased in calendar terms ("which billing month is this in?") and exactly what you don't want for SLA timers or precise durations. When you need complete elapsed units instead, use age(), the complement: age('day', start, end) only counts a day once a full 24 hours has passed. Supported units range from nanosecond through year, and dateDiff has two aliases, date_diff and timestampDiff.
Notes
- dateDiff counts calendar-boundary crossings, not elapsed time: dateDiff('day', '2024-12-31 23:59:00', '2025-01-01 00:01:00') is 1 despite only two minutes passing.
- Use age(unit, start, end) as the complement — it counts complete elapsed units, so the same two-minute span returns 0 days.
- The aliases date_diff and timestampDiff are identical to dateDiff; pick one and stay consistent across a codebase.
- Supported units run from nanosecond to year; the result is always an Int64.
- Pass a fourth timezone argument to control which calendar the boundaries are measured in — this matters for day and larger units near midnight.
- In hypequery, wrap the call in selectExpr("dateDiff('day', a, b)", 'alias') inside .select([...]) — it is a raw ClickHouse expression, not a first-class builder method.
Example SQL
dateDiff in ClickHouse SQL
TypeScript with hypequery
Use dateDiff 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 dateDiff when you need them inside a builder query.
Common questions
What developers search for with dateDiff
Days to conversion in ClickHouse
To measure how long a signup takes to convert, dateDiff('day', created_at, converted_at) gives the day count. Remember it counts midnight crossings, so a signup at 23:00 converting at 01:00 the next day counts as 1 day.
Subscription age in months or years
dateDiff('month', started_at, now()) returns how many month boundaries a subscription has crossed — ideal for billing-cycle and cohort logic where calendar months, not elapsed 30-day windows, are what matter.
dateDiff counting wrong for SLA timers
If your SLA timer over-counts, you are hitting the boundary-crossing behavior: dateDiff('hour', ...) increments at each clock hour, not per 60 elapsed minutes. Use age('hour', start, end) when you need complete elapsed hours instead.
FAQ
Frequently asked questions about dateDiff
Does dateDiff measure elapsed time between two dates?
No. dateDiff counts how many `unit` boundaries are crossed between the two dates, not the elapsed duration. dateDiff('day', '2024-12-31 23:59:00', '2025-01-01 00:01:00') returns 1 because one midnight is crossed, even though only two minutes elapsed. Use age() when you need complete elapsed units.
What is the difference between dateDiff and age in ClickHouse?
dateDiff counts calendar-boundary crossings, so a partial unit still counts. age counts only fully completed units, so it returns the same value only once a whole unit of time has actually passed. They are complements: reach for dateDiff for calendar questions and age for precise durations.
What units and return type does dateDiff support?
dateDiff accepts units from nanosecond, microsecond, millisecond, second, minute, hour, day, week, month, quarter, up to year, and always returns an Int64. Its aliases date_diff and timestampDiff behave identically.
Next step
Use dateDiff in a type-safe TypeScript query
hypequery generates TypeScript types from your ClickHouse schema. Use dateDiff alongside the builder, and reach for raw SQL expressions when the function is not exposed as a dedicated helper.