Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 35 ยท Querying and Functions

Date, Time and String Functions

Learn the ClickHouse functions you use daily: toStartOfHour, toDate, dateDiff, formatDateTime, time zones, plus string, regex, URL and JSON extraction functions.

ClickHouse has more than a thousand built-in functions, and a handful of families cover most daily work. Time bucketing (toStartOfHour, toStartOfMonth) is the foundation of dashboards, and string, URL and JSON functions turn raw log lines into columns. Function names are case-sensitive (toDate, not TODATE) and follow a consistent naming style, so once you learn the pattern, guessing is reliable.

ClickHouse cannot run on this site. Function examples are read-only, and results are shown only for deterministic inputs where we are certain of them. The runnable blocks use the SQLite equivalents (strftime, substr, instr, json_extract), which show the same ideas with different names.

Converting and truncating dates

Types come first (data types): Date, DateTime, DateTime64. Conversion functions are named toType:

SQL
SELECT
    toDate('2026-05-03')                       AS d,        -- Date
    toDateTime('2026-05-03 14:25:10')          AS dt,       -- DateTime
    toDateTime64('2026-05-03 14:25:10.123', 3) AS dt64,
    toUnixTimestamp(dt)                        AS unix,
    toDate(dt)                                 AS back_to_date;

The toStartOf... family rounds a timestamp down to the beginning of a unit. It is how you bucket:

Function Example result for 2026-05-03 14:25:10
toStartOfMinute(t) 2026-05-03 14:25:00
toStartOfFifteenMinutes(t) 2026-05-03 14:15:00
toStartOfHour(t) 2026-05-03 14:00:00
toStartOfDay(t) 2026-05-03 00:00:00
toMonday(t) / toStartOfWeek(t) start of that week
toStartOfMonth(t) 2026-05-01
toStartOfQuarter(t), toStartOfYear(t) quarter or year start
toStartOfInterval(t, INTERVAL 5 MINUTE) any custom width
SQL
SELECT toStartOfInterval(event_time, INTERVAL 5 MINUTE) AS bucket,
       count() AS events
FROM page_views
GROUP BY bucket
ORDER BY bucket;

Bucketing works the same way in SQLite, using strftime to cut a string down:

SQL Stand-in: bucket by hour, day and month (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Parts, differences and arithmetic

SQL
SELECT
    toYear(t), toMonth(t), toDayOfMonth(t), toHour(t),
    toDayOfWeek(t),                    -- 1 = Monday ... 7 = Sunday
    toYYYYMM(t),                       -- 202605 as an integer
    dateDiff('day', a, b),             -- whole days between
    t + INTERVAL 7 DAY,                -- add an interval
    addMonths(t, 1), subtractHours(t, 3),
    now(), today(), yesterday()        -- non-deterministic: avoid in cached views

dateDiff('unit', start, end) counts unit boundaries crossed, so dateDiff('month', '2026-01-31', '2026-02-01') is 1 even though only a day passed. Pay attention when it matters.

SQL Stand-in: differences and age (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Time zones and formatting

A DateTime stores UTC seconds; a time zone changes only how you see and cut it. Day boundaries differ by zone, so state the zone when you bucket by day:

SQL
SELECT toStartOfDay(ts, 'Europe/Lisbon') AS local_day,
       toTimeZone(ts, 'Asia/Tokyo')      AS in_tokyo,
       formatDateTime(ts, '%Y-%m-%d %H:%i') AS pretty    -- %i is minutes, not %M
FROM events;

formatDateTime uses MySQL-style codes (%Y, %m, %d, %H, %i, %S), unlike SQLite's strftime where minutes are %M. Parse strings with parseDateTimeBestEffort('3 May 2026 2:25pm'), which accepts many human formats, or parseDateTime(s, '%Y-%m-%d') for a strict one.

String functions

SQL
SELECT
    length('hรฉllo'),              -- bytes: 6
    lengthUTF8('hรฉllo'),          -- characters: 5
    lower(s), upper(s), trimBoth(s),
    substring(s, 2, 3),           -- 1-based start, length
    position(s, 'x'),             -- 1-based, 0 if not found
    replaceAll(s, 'a', 'b'),
    concat('a', '-', 'b'),
    splitByChar('/', '/docs/a/b'),            -- array of parts
    arrayStringConcat(arr, ', '),
    startsWith(s, 'http'), endsWith(s, '.js'), s LIKE '%error%', s ILIKE '%ERROR%'

length counts bytes; use lengthUTF8 for characters. Most functions have a UTF8 variant (substringUTF8, reverseUTF8).

SQL Stand-in: split and find in strings (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Regular expressions

ClickHouse uses the RE2 engine (no backreferences or lookarounds, but guaranteed linear time):

SQL
SELECT
    match(s, '^GET /api/'),                    -- true or false
    extract(s, '(\\d{3})$'),                   -- first capture group
    extractAll(s, '\\w+'),                     -- all matches
    replaceRegexpAll(s, '\\d+', '#'),
    multiMatchAny(s, ['error', 'fatal'])       -- fast for many patterns

Backslashes are doubled inside SQL string literals ('\\d+'). Cheap string functions beat regex, so prefer startsWith, position or LIKE where they are enough.

URL and JSON helpers

SQL
SELECT
    domain('https://www.example.com/a/b?x=1#top'),     -- www.example.com
    domainWithoutWWW(url), path(url), queryString(url),
    extractURLParameter(url, 'utm_source'),
    protocol(url), topLevelDomain(url);

SELECT
    JSONExtractString(payload, 'user', 'name') AS name,   -- nested path
    JSONExtractInt(payload, 'age')             AS age,
    JSONExtractRaw(payload, 'items')           AS raw,
    JSONHas(payload, 'email')                  AS has_email
FROM events;

These parse a String on every call. For hot paths, extract the fields into real columns at insert time (a MATERIALIZED column or a materialized view) or use the JSON type.

SQL Stand-in: pull fields out of JSON text (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

Common beginner mistakes

  • Using length for characters on non-ASCII text.
  • Bucketing by day without a time zone and getting UTC days.
  • Confusing %i (minutes) and %M (month name) in formatDateTime.
  • Calling now() inside a view or default that should be deterministic.
  • Parsing JSON text in every query instead of extracting columns once.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which function rounds a timestamp down to the start of its hour?

    The toStartOf family truncates; toHour only extracts the hour number.
  2. 2. What is the difference between length and lengthUTF8 on the string 'hรฉllo'?

    The accented e takes two bytes in UTF-8.
  3. 3. What does dateDiff('month', '2026-01-31', '2026-02-01') return?

    dateDiff counts unit boundaries crossed, and a month boundary was crossed.
  4. 4. Which regex engine does ClickHouse use?

    RE2 is fast and safe against pathological patterns, but limited.

Summary

  • toDate, toDateTime convert; toStartOf* truncates for bucketing; dateDiff counts boundaries crossed.
  • State a time zone when bucketing by day; formatDateTime uses %i for minutes.
  • length is bytes; use lengthUTF8 for characters; regex is RE2.
  • domain, extractURLParameter and JSONExtract* turn raw text into columns; extract once, not per query.

Next: Arrays and lambda functions