Working with timestamps and dates in ClickHouse® means you'll inevitably need to add or subtract time intervals, whether you're calculating retention periods, building time-series comparisons, or filtering data by relative date ranges. ClickHouse® offers multiple approaches for date arithmetic, from simple helper functions like addDays() to SQL-standard DATE_ADD syntax.
This guide covers the practical syntax for each method, common edge cases that can trip you up in production, and performance considerations when working with large datasets.
Quick Reference to ClickHouse® Date Arithmetic Functions
ClickHouse® gives you several ways to add or subtract time intervals from dates and datetimes. You can use helper functions like addDays() and subtractMonths(), or the SQL-standard DATE_ADD and DATE_SUB functions with interval specifications.
The helper functions follow a simple pattern: the function name tells you the operation and time unit, then you provide the date value and the number of units to add or subtract. ClickHouse® handles tricky cases like leap years and month-end rollovers automatically.
addSeconds and subtractSeconds
The addSeconds() and subtractSeconds() functions work with both Date and DateTime types, though they're most useful when you're working with timestamps that include time components.
SELECT
addSeconds(now(), 30) AS thirty_seconds_later,
subtractSeconds(now(), 3600) AS one_hour_ago;
When you use Date types with these functions, ClickHouse® first converts the date to a DateTime at midnight before doing the math.
addMinutes and subtractMinutes
Minute arithmetic comes in handy for calculating time windows in real-time analytics scenarios.
SELECT
addMinutes(toDateTime('2024-01-15 14:30:00'), 45) AS meeting_end,
subtractMinutes(now(), 15) AS fifteen_minutes_ago;
You'll often use these functions in WHERE clauses to filter data within specific time ranges, like finding events from the last 30 minutes.
addHours and subtractHours
Hour-based calculations are common for daily reporting and time-based data segmentation.
SELECT
addHours(toDateTime('2024-01-15 09:00:00'), 8) AS end_of_workday,
subtractHours(now(), 6) AS six_hours_ago;
The functions respect timezone settings when working with DateTime64 types that include timezone information.
addDays and subtractDays
Day arithmetic represents the most frequently used date functions in ClickHouse® queries. The functions work seamlessly with both Date and DateTime types.
SELECT
addDays(today(), 7) AS next_week,
subtractDays(toDate('2024-01-15'), 30) AS thirty_days_prior;
The subtractDays() function appears constantly in analytical queries for comparing current data with historical periods.
addWeeks and subtractWeeks
Week-based functions provide a convenient shorthand for 7-day intervals, especially useful in business reporting contexts.
SELECT
addWeeks(today(), 2) AS two_weeks_from_now,
subtractWeeks(toDate('2024-01-15'), 4) AS four_weeks_ago;
The functions always add or subtract exactly 7 days, regardless of how weeks align with month boundaries.
addMonths and subtractMonths
Month arithmetic requires special handling for edge cases like February 29th or January 31st plus one month.
SELECT
addMonths(toDate('2024-01-31'), 1) AS february_result,
subtractMonths(toDate('2024-03-31'), 1) AS february_from_march;
ClickHouse® handles invalid dates by moving to the last valid day of the target month. Adding one month to January 31st yields February 29th in leap years or February 28th otherwise.
addYears and subtractYears
Year arithmetic functions handle leap year transitions automatically, making them reliable for long-term date calculations.
SELECT
addYears(toDate('2024-02-29'), 1) AS next_year_leap_day,
subtractYears(today(), 5) AS five_years_ago;
When adding years to February 29th, the result becomes February 28th in non-leap years.
Using date_add and date_sub Aliases in SQL Queries
The DATE_ADD and DATE_SUB functions provide SQL-standard alternatives to the helper functions, accepting an interval unit as a parameter. The aliases offer more flexibility when the interval type comes from a variable or calculation.
SELECT
DATE_ADD(HOUR, 3, now()) AS three_hours_later,
DATE_SUB(DAY, 7, today()) AS last_week;
Supported Units and Abbreviations
ClickHouse® supports several interval units with DATE_ADD and DATE_SUB:
- SECOND: Add or subtract seconds from DateTime values
- MINUTE: Minute-level arithmetic for time calculations
- HOUR: Hour-based intervals for daily operations
- DAY: Most common unit for date arithmetic
- WEEK: Seven-day intervals for weekly reporting
- MONTH: Monthly calculations with automatic edge case handling
- QUARTER: Three-month intervals for business reporting
- YEAR: Annual arithmetic with leap year awareness
The unit names are case-insensitive, so day, DAY, and Day all work identically.
Sample Query Comparing Alias and Helper
Both approaches produce identical results, but the syntax differs in readability and parameter order:
SELECT
-- Using helper functions
addDays(today(), -7) AS last_week_helper,
subtractDays(today(), 7) AS last_week_subtract,
-- Using DATE_ADD/DATE_SUB aliases
DATE_ADD(DAY, -7, today()) AS last_week_add_alias,
DATE_SUB(DAY, 7, today()) AS last_week_sub_alias;
The helper functions place the date first, while the aliases follow SQL standard order with the interval unit first.
Choosing Between Helper Functions and Interval Literals
ClickHouse® also supports INTERVAL literal syntax for date arithmetic, similar to PostgreSQL and other SQL databases. The choice between approaches often depends on query complexity and readability preferences.
-- INTERVAL literal syntax
SELECT
today() + INTERVAL 1 MONTH AS next_month,
now() - INTERVAL 2 HOUR AS two_hours_ago;
When to Prefer Interval Literals
INTERVAL literals shine when combining multiple time units in a single expression or when the syntax feels more natural to read.
SELECT
now() + INTERVAL 1 MONTH + INTERVAL 2 DAY AS complex_future_date,
created_at - INTERVAL 1 YEAR - INTERVAL 6 MONTH AS eighteen_months_ago
FROM events;
This approach becomes particularly readable when building complex date expressions that involve multiple different interval types.
When Helper Functions Are Clearer
Helper functions work better when the interval value comes from a column, parameter, or calculation rather than a literal constant.
SELECT
addDays(order_date, shipping_days) AS estimated_delivery,
subtractMonths(today(), report_period_months) AS report_start_date
FROM orders;
The helper function syntax also tends to be more discoverable through IDE autocomplete and feels more natural when the interval is dynamic.
Common Pitfalls With Months, Years, and Time Zones
Date arithmetic becomes tricky when dealing with variable-length months, leap years, and timezone transitions. Understanding edge cases prevents unexpected results in production queries.
February and Leap Years
Adding months to dates near month boundaries can produce surprising results, especially around February in leap years.
SELECT
addMonths(toDate('2024-01-31'), 1) AS jan_31_plus_month, -- 2024-02-29
addMonths(toDate('2023-01-31'), 1) AS jan_31_non_leap; -- 2023-02-28
ClickHouse® resolves invalid dates by finding the last valid day of the target month, which explains why January 31st plus one month becomes February 28th or 29th.
End-of-Month Rollovers
Month arithmetic with dates like March 31st can create unexpected rollover behavior when the target month has fewer days.
SELECT
addMonths(toDate('2024-03-31'), 1) AS march_31_plus_month, -- 2024-04-30
addMonths(toDate('2024-05-31'), 1) AS may_31_plus_month; -- 2024-06-30
This behavior is consistent but can catch developers off guard when building month-over-month comparison queries.
Daylight Saving Transitions
Hour arithmetic during daylight saving transitions can produce times that don't exist or exist twice, depending on your timezone settings.
-- This query behavior depends on your timezone setting
SELECT
addHours(toDateTime('2024-03-10 01:30:00'), 1) AS spring_forward,
addHours(toDateTime('2024-11-03 01:30:00'), 1) AS fall_back;
For consistent behavior across timezone changes, consider using UTC timestamps for calculations and converting to local time only for display.
Performance Tips for High-Volume Date Math
Date arithmetic performance becomes critical when processing millions of rows or running frequent analytical queries. A few optimization strategies can significantly improve query execution times.
Avoiding Implicit Casts
Using proper DateTime types instead of strings prevents expensive implicit conversions during date arithmetic operations.
-- Slower: string conversion required
SELECT addDays('2024-01-15', 7) AS next_week;
-- Faster: native DateTime type
SELECT addDays(toDate('2024-01-15'), 7) AS next_week;
When possible, store timestamps as DateTime or DateTime64 types in your tables rather than strings to avoid conversion overhead.
Using Constant Folding
ClickHouse® can optimize date arithmetic expressions involving constants at query planning time rather than computing them for each row.
-- Optimized: constant folded at planning time
SELECT * FROM events
WHERE created_at >= subtractDays(today(), 30);
-- Less optimal: computed per row
SELECT * FROM events
WHERE created_at >= subtractDays(created_at, days_offset);
Place constant date calculations in the WHERE clause when filtering large datasets for better performance.
Pre-Computing Intervals in Materialized Views
For frequently-accessed date ranges, consider pre-computing the intervals in materialized views to avoid repeated calculations.
CREATE MATERIALIZED VIEW daily_metrics_mv
ENGINE = SummingMergeTree()
ORDER BY (date, metric_type)
AS SELECT
toDate(timestamp) AS date,
metric_type,
sum(value) AS total_value
FROM raw_events
GROUP BY date, metric_type;
This approach works particularly well for time-series dashboards that repeatedly query the same date ranges.
Building ClickHouse®-based time series analytics APIs with Tinybird
Transform your date arithmetic queries into comprehensive customer lifecycle analytics using Tinybird's managed ClickHouse® platform. This approach enables real-time cohort analysis, retention tracking, and customer journey insights across multiple time dimensions.
Instead of managing ClickHouse® infrastructure, you define your customer analytics logic as code and Tinybird handles the operational complexity. The platform provides sub-second query performance for complex time-based customer analytics while abstracting away database administration.
1. Create a data source for customer lifecycle events
Define your customer lifecycle data structure in a data source file:
SCHEMA >
`timestamp` DateTime64(3, 'UTC') `json:$.timestamp`,
`customer_id` String `json:$.customer_id`,
`event_type` LowCardinality(String) `json:$.event_type`,
`session_id` String `json:$.session_id`,
`product_id` String `json:$.product_id`,
`revenue` Decimal(10, 2) `json:$.revenue`,
`page_views` UInt32 `json:$.page_views`,
`time_on_site` UInt32 `json:$.time_on_site`,
`device_type` LowCardinality(String) `json:$.device_type`,
`country` LowCardinality(String) `json:$.country`,
`subscription_tier` LowCardinality(String) `json:$.subscription_tier`,
`lifetime_value` Decimal(12, 2) `json:$.lifetime_value`,
`churn_risk_score` Float32 `json:$.churn_risk_score`
ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(timestamp)"
ENGINE_SORTING_KEY "timestamp, customer_id"
This schema captures comprehensive customer lifecycle data including events, revenue, engagement metrics, and predictive indicators for advanced customer analytics.
2. Ingest example data using the Events API
Add sample customer lifecycle data to test your analytics using the Events API. First, start your local Tinybird development environment:
# Start Tinybird locally
tb dev
Then ingest sample data to your local instance:
# Ingest sample customer lifecycle data to localhost
curl -X POST "http://localhost:7181/v0/events" \
-H "Authorization: Bearer $TINYBIRD_TOKEN" \
-H "Content-Type: application/json" \
-d '[
{
"timestamp": "2024-01-15T10:30:00.000Z",
"customer_id": "cust_001",
"event_type": "signup",
"session_id": "sess_001",
"product_id": "prod_premium",
"revenue": 99.99,
"page_views": 12,
"time_on_site": 1800,
"device_type": "desktop",
"country": "US",
"subscription_tier": "premium",
"lifetime_value": 99.99,
"churn_risk_score": 0.1
},
{
"timestamp": "2024-01-15T11:00:00.000Z",
"customer_id": "cust_002",
"event_type": "purchase",
"session_id": "sess_002",
"product_id": "prod_basic",
"revenue": 29.99,
"page_views": 8,
"time_on_site": 1200,
"device_type": "mobile",
"country": "CA",
"subscription_tier": "basic",
"lifetime_value": 29.99,
"churn_risk_score": 0.3
},
{
"timestamp": "2024-01-15T12:00:00.000Z",
"customer_id": "cust_003",
"event_type": "upgrade",
"session_id": "sess_003",
"product_id": "prod_enterprise",
"revenue": 199.99,
"page_views": 15,
"time_on_site": 2400,
"device_type": "desktop",
"country": "GB",
"subscription_tier": "enterprise",
"lifetime_value": 199.99,
"churn_risk_score": 0.05
}
]'
This sample data includes various customer lifecycle events across different subscription tiers, devices, and regions to test your analytics pipes.
3. Build customer lifecycle analytics pipes
Create pipes that analyze customer behavior across multiple time dimensions:
NODE customer_events
SQL >
%
SELECT
timestamp,
customer_id,
event_type,
session_id,
product_id,
revenue,
page_views,
time_on_site,
device_type,
country,
subscription_tier,
lifetime_value,
churn_risk_score,
-- Date arithmetic for cohort analysis
toDate(timestamp) AS event_date,
toStartOfMonth(timestamp) AS cohort_month,
toStartOfWeek(timestamp, 1) AS cohort_week,
subtractDays(timestamp, 30) AS thirty_days_ago,
subtractDays(timestamp, 90) AS ninety_days_ago,
subtractDays(timestamp, 365) AS one_year_ago
FROM customer_events
WHERE timestamp >= {{DateTime(start_date, '2024-01-01 00:00:00')}}
AND timestamp <= {{DateTime(end_date, '2024-12-31 23:59:59')}}
{\% if defined(customer_filter) %}
AND customer_id = {{String(customer_filter)}}
{\% end %}
{\% if defined(event_type_filter) %}
AND event_type = {{String(event_type_filter)}}
{\% end %}
{\% if defined(country_filter) %}
AND country = {{String(country_filter)}}
{\% end %}
NODE customer_cohort_analysis
SQL >
%
SELECT
cohort_month,
subscription_tier,
country,
count(DISTINCT customer_id) AS total_customers,
count() AS total_events,
sum(revenue) AS total_revenue,
round(avg(lifetime_value), 2) AS avg_lifetime_value,
round(avg(churn_risk_score) * 100, 2) AS avg_churn_risk_percent,
round(avg(time_on_site), 2) AS avg_time_on_site,
round(avg(page_views), 2) AS avg_page_views,
-- Cohort retention metrics
count(DISTINCT CASE
WHEN timestamp >= addDays(cohort_month, 30)
AND timestamp < addDays(cohort_month, 60)
THEN customer_id
END) AS retained_30_days,
count(DISTINCT CASE
WHEN timestamp >= addDays(cohort_month, 90)
AND timestamp < addDays(cohort_month, 120)
THEN customer_id
END) AS retained_90_days,
-- Growth calculations
round((sum(revenue) - lag(sum(revenue)) OVER (PARTITION BY subscription_tier, country ORDER BY cohort_month)) * 100.0 / lag(sum(revenue)) OVER (PARTITION BY subscription_tier, country ORDER BY cohort_month), 2) AS revenue_growth_percent
FROM customer_events
GROUP BY cohort_month, subscription_tier, country
ORDER BY cohort_month DESC, total_revenue DESC
{\% if defined(limit) %}
LIMIT {{Int32(limit, 50)}}
{\% end %}
TYPE ENDPOINT
Create a customer journey analysis pipe:
NODE customer_journey_analysis
SQL >
%
SELECT
customer_id,
subscription_tier,
country,
min(timestamp) AS first_event,
max(timestamp) AS last_event,
count() AS total_events,
sum(revenue) AS total_revenue,
round(avg(churn_risk_score) * 100, 2) AS avg_churn_risk_percent,
round(avg(time_on_site), 2) AS avg_time_on_site,
round(avg(page_views), 2) AS avg_page_views,
-- Journey duration calculations
dateDiff('day', min(timestamp), max(timestamp)) AS journey_days,
dateDiff('hour', min(timestamp), max(timestamp)) AS journey_hours,
-- Recent activity indicators
CASE
WHEN max(timestamp) >= subtractDays(now(), 7) THEN 'active'
WHEN max(timestamp) >= subtractDays(now(), 30) THEN 'recent'
WHEN max(timestamp) >= subtractDays(now(), 90) THEN 'dormant'
ELSE 'inactive'
END AS activity_status,
-- Customer segmentation
CASE
WHEN sum(revenue) >= 500 AND avg(churn_risk_score) < 0.2 THEN 'high_value_low_risk'
WHEN sum(revenue) >= 200 AND avg(churn_risk_score) < 0.4 THEN 'medium_value_medium_risk'
WHEN sum(revenue) < 100 OR avg(churn_risk_score) >= 0.6 THEN 'low_value_high_risk'
ELSE 'standard'
END AS customer_segment
FROM customer_events
GROUP BY customer_id, subscription_tier, country
ORDER BY total_revenue DESC, avg_churn_risk_percent ASC
{\% if defined(limit) %}
LIMIT {{Int32(limit, 100)}}
{\% end %}
TYPE ENDPOINT
4. Test locally and deploy to cloud
Test your customer lifecycle analytics APIs locally first:
# Test customer cohort analysis locally
curl "http://localhost:7181/v0/pipes/customer_cohort_analysis.json?start_date=2024-01-01&end_date=2024-01-31&limit=10" \
-H "Authorization: Bearer $TINYBIRD_TOKEN"
Here's an example API response:
{
"meta": [
{
"name": "cohort_month",
"type": "Date"
},
{
"name": "subscription_tier",
"type": "String"
},
{
"name": "country",
"type": "String"
},
{
"name": "total_customers",
"type": "UInt64"
},
{
"name": "total_events",
"type": "UInt64"
},
{
"name": "total_revenue",
"type": "Decimal(10,2)"
},
{
"name": "avg_lifetime_value",
"type": "Float64"
},
{
"name": "avg_churn_risk_percent",
"type": "Float64"
},
{
"name": "avg_time_on_site",
"type": "Float64"
},
{
"name": "avg_page_views",
"type": "Float64"
},
{
"name": "retained_30_days",
"type": "UInt64"
},
{
"name": "retained_90_days",
"type": "UInt64"
},
{
"name": "revenue_growth_percent",
"type": "Float64"
}
],
"data": [
{
"cohort_month": "2024-01-01",
"subscription_tier": "premium",
"country": "US",
"total_customers": 1,
"total_events": 1,
"total_revenue": 99.99,
"avg_lifetime_value": 99.99,
"avg_churn_risk_percent": 10.0,
"avg_time_on_site": 1800.0,
"avg_page_views": 12.0,
"retained_30_days": 0,
"retained_90_days": 0,
"revenue_growth_percent": null
}
],
"rows": 1,
"statistics": {
"elapsed": 0.001234,
"rows_read": 3,
"bytes_read": 1024
}
}
Once you're satisfied with your local testing, deploy to Tinybird's cloud infrastructure:
# Deploy to cloud
tb --cloud deploy
Test your production APIs:
# Customer cohort analysis
curl "https://api.tinybird.co/v0/pipes/customer_cohort_analysis.json?start_date=2024-01-01&end_date=2024-06-30&subscription_tier=premium&limit=20" \
-H "Authorization: Bearer $TINYBIRD_TOKEN"
# Customer journey analysis
curl "https://api.tinybird.co/v0/pipes/customer_journey_analysis.json?start_date=2024-01-01&end_date=2024-12-31&country_filter=US&limit=50" \
-H "Authorization: Bearer $TINYBIRD_TOKEN"
The APIs return comprehensive customer lifecycle insights including cohort retention, journey analysis, revenue trends, churn risk assessment, and customer segmentation. This enables real-time customer analytics across multiple time dimensions, subscription tiers, and geographic regions for data-driven customer success strategies.
Wrap up and next steps with managed ClickHouse®
While ClickHouse®'s date arithmetic functions are powerful, operating ClickHouse® at scale introduces significant operational complexity. Managing clusters, handling replication, optimizing queries, and ensuring high availability requires dedicated engineering resources and deep ClickHouse® expertise.
Production ClickHouse® deployments face challenges like:
- Cluster Management: Coordinating multiple nodes, handling failovers, and maintaining data consistency across replicas
- Query Optimization: Tuning performance for complex date arithmetic across millions of rows while maintaining sub-second response times
- Infrastructure Scaling: Automatically scaling resources during traffic spikes without downtime
- Monitoring & Alerting: Tracking query performance, resource usage, and system health across distributed nodes
- Backup & Recovery: Implementing reliable backup strategies and disaster recovery procedures
Tinybird eliminates these operational burdens by providing managed ClickHouse® infrastructure with built-in optimizations for real-time analytics. The platform handles cluster management, automatic scaling, and query optimization while providing a streamlined developer experience for building analytics APIs.
Start with Tinybird's free plan and deploy your first analytics API in minutes.
FAQs About ClickHouse® Interval Arithmetic
Why does subtractMonths sometimes return a date in the next month?
ClickHouse® handles invalid dates by moving to the last valid day of the target month, which can appear as moving forward when subtracting. For example, subtracting one month from March 31st yields February 28th or 29th, but the day-of-month decreased even though it might seem like the function moved "forward" to a valid February date.
Can I use negative values with addDays?
Yes, addDays(date, -7) is equivalent to subtractDays(date, 7) and both produce identical results. This pattern works for all the helper functions; negative values in add functions behave exactly like positive values in subtract functions.
Is date_add available in older ClickHouse® versions?
The DATE_ADD and DATE_SUB aliases were introduced in ClickHouse® 21.x, but the underlying helper functions work in all modern versions. If you're using an older ClickHouse® version, stick with functions like addDays() and subtractMonths() for compatibility.
Additional resources
- Date and Time Functions
- addDays Function
- subtractMonths Function
- INTERVAL Literals
- Materialized Views
- Tinybird vs ClickHouse®
- How to calculate date differences using unit boundaries with dateDiff in ClickHouse®
- ClickHouse® age function for timestamp difference
- Convert date to Unix timestamp in ClickHouse®
- Parse numeric date formats in ClickHouse®