--- title: "How to calculate date differences using dateDiff in ClickHouse®" excerpt: "Learn how ClickHouse® dateDiff counts unit boundaries, not elapsed time, with practical examples for cohort analysis, billing cycles, and common pitfalls to avoid." authors: "Cameron Archer" categories: "AI Resources" createdOn: "2025-09-18 16:20:55" publishedOn: "2025-09-18 16:20:55" updatedOn: "2025-09-18 16:20:55" status: "published" --- 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{% sup %}®{% /sup %}, 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{% sup %}®{% /sup %} dateDiff and how it counts unit boundaries The `dateDiff()` function in ClickHouse{% sup %}®{% /sup %} 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. ```sql 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{% sup %}®{% /sup %} 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{% sup %}®{% /sup %} 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: ```sql 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: ```sql 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: ```sql 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{% sup %}®{% /sup %}. Recognizing these patterns helps avoid errors in boundary calculations. ### Unquoted unit string ClickHouse{% sup %}®{% /sup %} requires the unit parameter as a quoted string. An unquoted unit causes a syntax error: ```sql -- 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: ```sql SELECT dateDiff('month', '2024-09-18', '2024-06-18') AS diff; -- Returns: -3 ``` For absolute differences regardless of direction, use the `abs()` function: ```sql 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: ```sql 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](https://tinybird.co/docs/forward/work-with-data/optimize/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: ```sql 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: ```sql 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{% sup %}®{% /sup %} date functions and alternatives ClickHouse{% sup %}®{% /sup %} 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. For adding or subtracting time intervals from dates (rather than calculating differences), use functions like `addDays()`, `subtractMonths()`, and `DATE_ADD`. These complement `dateDiff()` when you need to calculate future or past dates based on existing timestamps. Learn more about [adding and subtracting time intervals in ClickHouse{% sup %}®{% /sup %}](https://www.tinybird.co/blog-posts/how-to-add-and-subtract-time-intervals-clickhouse). ## Building date difference APIs with Tinybird [Tinybird's](https://www.tinybird.co) managed ClickHouse{% sup %}®{% /sup %} platform makes it straightforward to build APIs to calculate date differences and aggregates on time series data. The process involves creating a [data source](https://tinybird.co/docs/forward/get-data-in/data-sources), defining a parameterized query, and publishing it as a secure endpoint. Create a data source with your date columns using the [Tinybird CLI](https://tinybird.co/docs/forward/dev-reference/commands). Define a data source file and load sample data in NDJSON or CSV format. Then build a parameterized [pipe](https://tinybird.co/docs/forward/work-with-data/pipes) that uses `dateDiff()` with parameter substitution for flexible boundary calculations: ```tinybird 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](https://tinybird.co/docs/forward/work-with-data/publish-data/endpoints) and secure it with authentication [tokens](https://tinybird.co/docs/forward/administration/tokens). This creates a production-ready API for date boundary calculations without managing ClickHouse{% sup %}®{% /sup %} infrastructure directly. ## Developer analytics features faster with Tinybird's hosted ClickHouse{% sup %}®{% /sup %} Managing ClickHouse{% sup %}®{% /sup %} infrastructure involves complex setup, scaling, and maintenance tasks that grow with data requirements. Tinybird provides a [managed ClickHouse{% sup %}®{% /sup %} platform](https://www.tinybird.co/blog-posts/managed-clickhouse-options) 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{% sup %}®{% /sup %} at [Tinybird Cloud](https://cloud.tinybird.co/signup). ## FAQs about ClickHouse{% sup %}®{% /sup %} 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.