Working with dates in databases often involves finding the difference between two points in time. Sometimes, it is important to know how many days, months, or years separate one date from another. In ClickHouse, the dateDiff() function provides a way to calculate these differences.
However, dateDiff() does something specific: it counts unit boundaries crossed, not just elapsed time. This means the answer depends on how many times a date moves from one unit (like a month) to the next. As a result, the difference between two dates may not match the total time between them in hours or seconds.
Understanding how dateDiff() works is helpful when working with reporting, cohort analysis, or any scenario where calendar boundaries matter.
What is ClickHouse dateDiff and how it counts unit boundaries
The dateDiff() function in ClickHouse calculates the number of calendar unit boundaries crossed between two dates. When given two dates and a unit (such as day, month, or year), it returns an integer showing how many times that boundary is passed moving from the start date to the end date.
For example, using dateDiff('month', '2024-01-31', '2024-02-01') gives a result of 1. This is because the calculation starts in January and crosses into February, which counts as one month boundary crossed, even though only one day passes.
The function treats each boundary as a discrete jump, not as an accumulation of elapsed time. If the two dates are within the same unit, the result is 0. If the dates are reversed (the end date comes before the start date), the result is negative.
Syntax and return types for dateDiff
The dateDiff function uses this signature: dateDiff('unit', start_date, end_date). The first argument is the unit as a string, the second is the start date, and the third is the end date.
SELECT dateDiff('month', '2024-01-31', '2024-02-01')
This returns 1 because there is one month boundary crossed between January 31 and February 1, 2024.
The function returns a signed integer. If the end date is after the start date, the result is positive. If the end date comes before the start date, the result is negative. The dateDiff function works with several ClickHouse date types: Date, Date32, and DateTime.
Supported units and aliases for dateDiff
The dateDiff() function supports several time units, each with accepted aliases that can be used interchangeably:
- Day units:
'day','days','d'— counts calendar day boundaries crossed - Week units:
'week','weeks','wk','ww'— counts week boundaries (Monday to Sunday) - Month units:
'month','months','m'— counts month boundaries, not the total number of days - Quarter units:
'quarter','quarters','q'— counts quarterly boundaries (January, April, July, October) - Year units:
'year','years','yy','yyyy'— counts year boundaries crossed
How unit boundary counting differs from elapsed time
The dateDiff() function counts calendar boundaries, not elapsed time. This produces different results than subtracting one date from another to get total time in days or seconds.
Consider dateDiff('month', '2023-01-31', '2023-02-01') which returns 1. Only one day passes between January 31 and February 1, but the function counts that the date moved from January to February - one month boundary crossed.
A calculation measuring elapsed time would treat this same example as one day, not one month. The boundary-counting approach can produce surprising results if you expect a continuous measure of time rather than discrete transitions between calendar units.
When ClickHouse calculates boundaries, it checks the calendar position of both dates. The function determines how many times the starting date moves from one unit into the next before reaching the end date. It doesn't examine total days or hours between dates - just boundary crossings.
Examples using dateDiff for common boundary scenarios
Here are practical examples showing how dateDiff() handles different boundary crossing patterns in real analytics scenarios.
Cohort age calculation
A common task is grouping users by months since signup for cohort analysis:
SELECT
user_id,
signup_date,
dateDiff('month', signup_date, today()) AS cohort_age_in_months
FROM users
If a user signed up on 2024-06-30 and today is 2024-09-18, the function returns 2. This counts crossing from June to July, then July to August. Since September 18 is still within September, only two boundaries have been crossed.
Month-end edge cases
Moving from January 31 to February 1 crosses the month boundary despite being only one day apart:
SELECT dateDiff('month', toDate('2024-01-31'), toDate('2024-02-01')) AS month_diff
-- Returns: 1
This happens because the calculation looks at whether dates are in different months, not elapsed days.
Year crossing calculations
Crossing from December 31 to January 1 counts as one year boundary:
SELECT dateDiff('year', '2022-12-31', '2023-01-01') AS year_diff
-- Returns: 1
The function returns 1 because dates cross from 2022 to 2023, even though only one day separates them.
Common dateDiff mistakes and fixes
Several mistakes commonly occur when working with dateDiff() in ClickHouse. Recognizing these patterns helps avoid errors in boundary calculations.
Unquoted unit string
ClickHouse requires the unit parameter as a quoted string. An unquoted unit causes a syntax error:
-- Incorrect: Causes syntax error
SELECT dateDiff(day, '2024-09-01', '2024-09-18');
-- Correct: Returns number of day boundaries crossed
SELECT dateDiff('day', '2024-09-01', '2024-09-18');
Reversed start and end dates
Date order matters in dateDiff(). If the end date comes before the start date, the result is negative:
SELECT dateDiff('month', '2024-09-18', '2024-06-18') AS diff;
-- Returns: -3
For absolute differences regardless of direction, use the abs() function:
SELECT abs(dateDiff('month', '2024-09-18', '2024-06-18')) AS abs_diff;
-- Returns: 3
Time zone inconsistencies
Mixing different time zones can cause unexpected boundary counts. Convert all DateTime values to the same time zone for consistent results:
SELECT dateDiff('day',
toTimeZone(toDateTime('2024-03-30 12:00:00', 'Europe/Berlin'), 'UTC'),
toTimeZone(toDateTime('2024-03-31 12:00:00', 'Europe/Berlin'), 'UTC')
);
This prevents daylight saving time changes from affecting boundary calculations unexpectedly.
Performance patterns for dateDiff on large tables
Two techniques help optimize boundary calculations on large datasets: materialized views for precomputing results and LowCardinality columns for unit strings.
Precompute with materialized views
When calculations like dateDiff('month', signup_date, current_date) are used frequently, create a materialized view that precomputes values:
CREATE MATERIALIZED VIEW users_cohort_by_month
ENGINE = MergeTree
ORDER BY user_id
AS
SELECT
user_id,
signup_date,
dateDiff('month', signup_date, today()) AS cohort_months
FROM users
Queries can read from users_cohort_by_month instead of recalculating cohort age for every user.
Optimize unit strings with LowCardinality
If unit values are stored in table columns and used repeatedly, store them as LowCardinality(String). This reduces memory usage and speeds up queries when the same unit values appear many times:
CREATE TABLE events_with_units
(
event_id UInt64,
event_time DateTime,
reference_time DateTime,
unit LowCardinality(String)
) ENGINE = MergeTree
ORDER BY event_id
Related ClickHouse date functions and alternatives
ClickHouse offers several functions for date calculations beyond dateDiff(). Understanding when to use each helps choose the right approach.
The toRelativeDayNum() and toRelativeMonthNum() functions return numbers representing date positions in an abstract timeline. Subtracting these results gives total units between dates without counting calendar boundaries the same way as dateDiff().
The dateTrunc() function rounds dates down to the start of specified calendar units. For example, dateTrunc('month', '2024-09-18 10:30:00') returns 2024-09-01 00:00:00. This works well for grouping timestamps into buckets for aggregation, while dateDiff() counts transitions between specific dates.
Building date difference APIs with Tinybird
Tinybird's managed ClickHouse platform makes it straightforward to build APIs to calculate date differences and aggregates on time series data.
The process involves creating a data source, defining a parameterized query, and publishing it as a secure endpoint.
Create a data source with your date columns using the Tinybird CLI. Define a data source file and load sample data in NDJSON or CSV format.
Then build a parameterized pipe that uses dateDiff() with parameter substitution for flexible boundary calculations:
NODE date_diff
SQL >
%
SELECT
id,
event_date,
reference_date,
dateDiff({{ String(unit, 'day') }}, event_date, reference_date) AS diff
FROM events
WHERE id = {{ UInt32(id) }}
TYPE endpoint
Deploy the pipe as an API endpoint and secure it with authentication tokens.
This creates a production-ready API for date boundary calculations without managing ClickHouse infrastructure directly.
Developer analytics features faster with Tinybird's hosted ClickHouse
Managing ClickHouse infrastructure involves complex setup, scaling, and maintenance tasks that grow with data requirements. Tinybird provides a managed ClickHouse platform handling deployment, scaling, and upgrades automatically.
Date functions like dateDiff() run on infrastructure kept up-to-date and optimized by the Tinybird team. This removes the need to learn server configuration, storage tuning, or operational monitoring to get reliable results with date calculations.
Tinybird also provides security, compliance features, and development workflows including data versioning, live schema migration, and instant API publishing. Get started with managed ClickHouse at Tinybird Cloud.
FAQs about ClickHouse dateDiff
Does dateDiff count the starting date boundary?
The dateDiff() function counts boundaries crossed from start to end date, not including the starting boundary itself. Calculating from January 1 to January 2 returns 1 because one day boundary is crossed.
Can I use a column reference for the dateDiff unit parameter?
The unit parameter in dateDiff() must be a string literal in single quotes like 'day' or 'month'. Column names, variables, or expressions cannot be used for the unit parameter.
Why does dateDiff give unexpected results during daylight saving time?
The dateDiff() output can vary when using DateTime types affected by daylight saving time transitions. Convert all values to UTC or use Date types to maintain consistency across time zone changes.
