ClickHouse Date and Time Functions

ClickHouse stores time in a few related types and gives you a large family of functions to truncate, shift, difference, format, and parse them. This guide covers the functions you reach for most, the time-zone rules that decide what the numbers mean, and the rounding behavior that surprises people coming from PostgreSQL or MySQL.

The Types First

The functions only make sense once you know what they operate on.

• Date: a day, stored as days since the Unix epoch. Range 1970-01-01 to 2149-06-06.
• Date32: a wider day type, range 1900-01-01 to 2299-12-31.
• DateTime: second precision, stored as a Unix timestamp (UTC) plus an optional time-zone attribute.
• DateTime64(P): sub-second precision, where P is the number of fractional digits (3 = milliseconds, 6 = microseconds, 9 = nanoseconds).

A DateTime value is a UTC instant. The time zone attached to a column or returned by a function only changes how that instant is displayed and decomposed, not what is stored. That single fact explains most time-zone confusion below.

CREATE TABLE events
(
    event_id  UInt64,
    user_id   UInt32,
    ts        DateTime,            -- UTC instant
    ts_ms     DateTime64(3)        -- millisecond precision
)
ENGINE = MergeTree ORDER BY ts;

Current Time

SELECT
    now(),                 -- DateTime, second precision
    now64(3),              -- DateTime64(3), millisecond precision
    today(),               -- Date for the current day
    yesterday(),           -- Date for the previous day
    toUnixTimestamp(now()); -- seconds since epoch as UInt32

For SQL-standard compatibility, now, today, CURRENT_TIMESTAMP, and CURRENT_DATE can be written without parentheses. now() evaluates once per query, so two references in the same statement return the same value.

Truncating with toStartOf*

Truncation is the workhorse of analytics: it buckets rows into days, weeks, or months for grouping. ClickHouse exposes one function per granularity rather than a single DATE_TRUNC.

SELECT
    toStartOfMinute(ts)   AS minute,
    toStartOfHour(ts)     AS hour,
    toStartOfDay(ts)      AS day,
    toStartOfWeek(ts)     AS week,    -- Sunday by default
    toMonday(ts)          AS iso_week,-- week starting Monday
    toStartOfMonth(ts)    AS month,
    toStartOfQuarter(ts)  AS quarter,
    toStartOfYear(ts)     AS year
FROM events;

toStartOfWeek takes an optional mode argument controlling which day starts the week and how the first week is numbered, following the same mode table as toWeek. If you want ISO weeks starting Monday, toMonday is the direct route.

For arbitrary intervals, toStartOfInterval is the general form:

-- Bucket into 15-minute windows
SELECT
    toStartOfInterval(ts, INTERVAL 15 MINUTE) AS bucket,
    count() AS events
FROM events
GROUP BY bucket
ORDER BY bucket;
 
-- Five-minute and three-hour buckets work the same way
SELECT toStartOfInterval(ts, INTERVAL 3 HOUR) FROM events;

toStartOfInterval aligns buckets to a fixed origin (1970-01-01 for most units), so a 15-minute bucket always lands on :00, :15, :30, :45. That is usually what you want, but it means you cannot shift the phase without an explicit origin argument (supported in recent versions) or manual arithmetic.

Extracting Parts

SELECT
    toYear(ts),
    toMonth(ts),
    toDayOfMonth(ts),
    toDayOfWeek(ts),     -- 1 = Monday ... 7 = Sunday by default
    toHour(ts),
    toMinute(ts),
    toSecond(ts),
    toDayOfYear(ts),
    toQuarter(ts)
FROM events;

toDayOfWeek accepts a mode argument if you need Sunday-first or 0-based numbering. Always check the mode rather than assuming, because the default (Monday = 1) differs from MySQL's DAYOFWEEK (Sunday = 1).

Date Arithmetic

Add or subtract intervals with dateAdd / dateSub, or use the INTERVAL keyword directly:

SELECT
    dateAdd(DAY, 7, ts)        AS plus_one_week,
    dateSub(MONTH, 1, ts)      AS minus_one_month,
    ts + INTERVAL 30 MINUTE    AS plus_30_min,
    ts - INTERVAL 1 YEAR       AS one_year_ago
FROM events;

addDays, addWeeks, addMonths, subtractHours, and the rest of the family are shorthands for the same thing and read a little cleaner:

SELECT addMonths(ts, 3), subtractDays(ts, 14) FROM events;

Month and year arithmetic clamps to the end of the month, so adding one month to January 31 gives the last day of February rather than overflowing into March.

Differences with dateDiff

dateDiff returns the number of crossed unit boundaries between two values, not a rounded elapsed duration:

SELECT
    dateDiff('day',    toDate('2026-01-01'), toDate('2026-03-15')) AS days,   -- 73
    dateDiff('month',  toDate('2026-01-31'), toDate('2026-02-01')) AS months, -- 1
    dateDiff('second', toDateTime('2026-01-01 23:59:59'),
                       toDateTime('2026-01-02 00:00:01'))          AS seconds; -- 2

The month example is the trap: one day apart, but it crosses a month boundary, so the answer is 1. If you need true elapsed time, difference in the smallest unit (second or day) and divide. age is the inverse mindset, returning the elapsed unit count without counting partially crossed boundaries the same way; check your version's docs for the exact rounding before relying on either across month boundaries.

Formatting and Parsing

formatDateTime turns a value into a string using MySQL-style format codes:

SELECT
    formatDateTime(ts, '%Y-%m-%d %H:%M:%S') AS iso_like,
    formatDateTime(ts, '%d/%m/%Y')          AS european,
    formatDateTime(ts, '%Y-W%V')            AS iso_week_label
FROM events;

There is also formatDateTimeInJodaSyntax if you prefer Joda/Java patterns. Going the other direction, you have several parsers depending on how strict and how fast you need to be:

-- Strict, format-string driven (MySQL-style codes)
SELECT parseDateTime('15/06/2026 14:30:00', '%d/%m/%Y %H:%M:%S');
 
-- Best-effort: handles many common formats, returns 1970-01-01 on failure
SELECT parseDateTimeBestEffort('2026-06-15T14:30:22Z');
 
-- Best-effort variant that returns NULL instead of the epoch on failure
SELECT parseDateTimeBestEffortOrNull('not a date');
 
-- Packed numeric dates
SELECT YYYYMMDDToDate(20260615);
 
-- Unix timestamps
SELECT fromUnixTimestamp(1781000000);

parseDateTimeBestEffort is convenient for messy ingestion, but it silently maps unparseable input to 1970-01-01, which can hide bad data. The OrNull variant is safer in pipelines because a NULL is easy to filter and count.

Time Zones

This is where instinct from other databases fails. A DateTime is a UTC instant; the time zone only controls display and decomposition.

SELECT
    toDateTime('2016-06-15 23:00:00')                          AS server_local,
    toTimeZone(toDateTime('2016-06-15 23:00:00', 'UTC'),
               'Asia/Yekaterinburg')                           AS yekat;

toTimeZone does not move the instant in time; it returns the same instant labeled in a different zone, which changes the wall-clock fields you then extract. So toHour(toTimeZone(ts, 'America/New_York')) gives the New York hour for the same underlying moment. If your GROUP BY toStartOfDay(ts) produces day boundaries that look shifted, the cause is almost always the session or column time zone, not the function.

You can pin the zone explicitly on parse so results are reproducible regardless of server settings:

SELECT toStartOfDay(toTimeZone(ts, 'Europe/Zurich')) AS zurich_day
FROM events
GROUP BY zurich_day;

Common Mistakes

• Reading dateDiff('month', ...) as elapsed months. It counts boundary crossings. One day apart can return 1.
• Assuming toTimeZone shifts the timestamp. It relabels the same instant; the stored UTC value never changes.
• Trusting parseDateTimeBestEffort on dirty data. Failures become 1970-01-01. Use the OrNull variant and filter.
• Forgetting the week mode. toStartOfWeek defaults to Sunday; use toMonday for ISO weeks.
• Mixing Date and DateTime in arithmetic. Adding INTERVAL 1 HOUR to a Date promotes it to DateTime; know which type your column actually is before grouping on it.

When you are exploring an unfamiliar dataset and cannot remember whether the column is DateTime or DateTime64, Mako's AI autocomplete suggests the matching functions as you type, which shortens the trial-and-error loop on time-zone and truncation queries.

Related Guides

• ClickHouse Aggregate Functions
• ClickHouse Window Functions
• ClickHouse JOINs
• ClickHouse JSON Queries

Mako connects to ClickHouse with AI-powered autocomplete for queries like these. Try it free at mako.ai.