How to Work with Dates in ClickHouse

Last updated July 25, 2026 · By the SaturnSQL team

ClickHouse truncates dates with functions like toStartOfMonth, toStartOfWeek, and toStartOfDay, and formats period keys with toYYYYMM. Date arithmetic uses +/- INTERVAL, and dateDiff measures the gap between two timestamps.

SELECT toStartOfMonth(event_time) AS month, count() AS events
FROM events
GROUP BY month
ORDER BY month;

SELECT
  now() - INTERVAL 7 DAY AS week_ago,
  today() + INTERVAL 1 MONTH AS next_month,
  dateDiff('day', toDate('2026-01-01'), today()) AS days_since;