ClickHouse Timezone Handling: DateTime Parameters, Functions, and Conversions

ClickHouse stores all DateTime and DateTime64 values as timezone-agnostic Unix timestamps — the number of seconds (or sub-seconds) since 1970-01-01 00:00:00 UTC. The timezone is not stored with the data; it is metadata attached to the column type that controls only how the timestamp is rendered to text and how text is parsed back into a timestamp. Understanding that separation is the key to avoiding almost every timezone bug in ClickHouse.

This guide covers where the timezone comes from (column, session, and server levels), the conversion functions you need, how daylight saving time (DST) behaves, and the best practices for multi-region deployments.

How ClickHouse Stores Time

A DateTime column holds a single 32-bit Unix timestamp per value. The timezone you attach to the type — DateTime('Europe/Berlin') — never changes the bytes on disk. It changes two things:

1. Display: how the stored timestamp is formatted when you SELECT it as text or call toString().
2. Parsing: how a string literal like '2024-03-31 02:30:00' is interpreted when inserted into that column.

Because the underlying value is always UTC seconds, converting a column "to a different timezone" is purely a display operation — no data is rewritten.

SELECT
    toDateTime('2024-06-01 12:00:00', 'UTC')             AS utc,
    toTimeZone(toDateTime('2024-06-01 12:00:00', 'UTC'), 'Asia/Tokyo') AS tokyo;
-- Both rows represent the SAME instant; only the rendered text differs.

Where the Timezone Comes From

ClickHouse resolves the timezone for a value in a clear order of precedence:

If a column type has no timezone, ClickHouse falls back to session_timezone (if set), then to the server's timezone setting, then to the operating system timezone read at server start.

Server-level timezone

Set the server default in the configuration file. This applies to every column that does not specify its own zone:

<clickhouse>
    <timezone>UTC</timezone>
</clickhouse>

Check what the server is actually using with serverTimezone() (alias serverTimeZone()), which returns the value of the server timezone setting:

SELECT serverTimezone();

Session-level timezone

session_timezone overrides the server default for the current session. It affects how columns without an explicit timezone are rendered, and how string timestamps are parsed:

SET session_timezone = 'America/New_York';
SELECT now(), timezone();   -- timezone() reflects the effective session zone

Use this with care: SET TIME ZONE 'tz' is an alias for this setting provided for PostgreSQL/SQL compatibility, but session_timezone itself is the native ClickHouse setting — and it changes the interpretation of every un-zoned DateTime in the session — including bare string literals — which can surprise downstream tooling.

Specifying a Timezone in CREATE TABLE

Attach a timezone to a column by passing it as the last argument to DateTime or DateTime64:

CREATE TABLE events
(
    event_id   UInt64,
    -- Stored as UTC seconds; rendered/parsed as UTC:
    created_at DateTime('UTC'),
    -- Same physical storage, but rendered/parsed in Berlin local time:
    local_ts   DateTime('Europe/Berlin'),
    -- Sub-second precision (milliseconds here) plus a timezone:
    precise_ts DateTime64(3, 'UTC')
)
ENGINE = MergeTree
ORDER BY (event_id, created_at);

The recommended pattern is to store everything in DateTime('UTC') (or DateTime64(n, 'UTC')) and convert to local time only at query or display time. See the CREATE TABLE reference for full column-definition syntax and the DateTime64 guide for sub-second precision details.

Conversion Functions

toTimeZone

toTimeZone(value, timezone) returns the same instant rendered in a different timezone. It does not change the underlying timestamp — only the attached display zone:

SELECT
    toTimeZone(now(), 'UTC')              AS in_utc,
    toTimeZone(now(), 'Australia/Sydney') AS in_sydney;

The optional timezone argument

Most date/time functions accept an optional final timezone argument. When supplied, the calculation is performed in that zone instead of the column's (or session/server) default. This matters because operations like "start of day" depend on the zone:

-- "Start of day" differs depending on the timezone the truncation is done in:
SELECT
    toStartOfDay(toDateTime('2024-06-01 23:30:00', 'UTC'), 'America/Los_Angeles') AS la_day,
    toStartOfDay(toDateTime('2024-06-01 23:30:00', 'UTC'), 'UTC')                 AS utc_day;

Functions such as toStartOfDay, toStartOfInterval, dateTrunc, toYear, formatDateTime, and toDate all accept this optional argument. See the dateTrunc function, toStartOfInterval, and formatDateTime pages for per-function details.

toUTCTimestamp and fromUTCTimestamp

toUTCTimestamp(value, timezone) and fromUTCTimestamp(value, timezone) convert between a wall-clock value in a given zone and UTC. They are included primarily for compatibility with Apache Spark-style semantics:

-- Treat the wall-clock value as Tokyo local time, return the UTC instant:
SELECT toUTCTimestamp(toDateTime('2024-06-01 12:00:00'), 'Asia/Tokyo');

-- Take a UTC instant and render it as Tokyo local time:
SELECT fromUTCTimestamp(toDateTime('2024-06-01 03:00:00'), 'Asia/Tokyo');

Unlike toTimeZone, these functions shift the wall-clock representation rather than re-labeling the same instant — choose deliberately.

Client vs. Server Conversion

A common source of confusion: the timestamp-to-text conversion can happen in different places depending on the client.

• Native protocol (clickhouse-client, native drivers): the client typically formats values. By default clickhouse-client uses the server timezone; pass --use_client_time_zone to format using the client's local zone instead.
• HTTP interface and string-producing expressions like toString(ts): conversion happens on the server, using the column/session/server zone.

The same query can therefore render differently across interfaces if the zones disagree. Being explicit — store UTC, convert with toTimeZone() in the query — removes the ambiguity entirely.

Daylight Saving Time (DST)

Because stored values are UTC, the timestamps themselves are never ambiguous. DST only affects calendar math and rendering in zones that observe it:

• Spring-forward gaps: a local wall-clock time like 2024-03-31 02:30:00 in Europe/Berlin does not exist (clocks jump from 02:00 to 03:00). Parsing such a string is undefined/normalized by ClickHouse.
• Fall-back duplicates: a local time like 2024-10-27 02:30:00 occurs twice. A single local string cannot distinguish the two instants.
• Per-day length changes: toStartOfDay / day-bucketed aggregations in a DST zone yield buckets of 23 or 25 hours on transition days, so count() / 24 style assumptions break.

Storing and aggregating in UTC sidesteps all three problems; apply the local-time conversion only on the final display step.

-- Robust: bucket in UTC, then label in local time for presentation.
SELECT
    toStartOfHour(toTimeZone(created_at, 'UTC')) AS hour_utc,
    count()
FROM events
GROUP BY hour_utc
ORDER BY hour_utc;

tzdata and Supported Zones

ClickHouse ships with embedded tzdata and uses it when the OS package is missing, so behavior is consistent across hosts. List every supported zone with:

SELECT * FROM system.time_zones;

Check the embedded tzdata version (important when a country changes its DST rules):

SELECT value FROM system.build_options WHERE name = 'TZDATA_VERSION';

Best Practices

1. Store everything in UTC. Use DateTime('UTC') or DateTime64(n, 'UTC') for stored columns. It removes ambiguity and makes server/client conversion irrelevant.
2. Convert only at the edges. Apply toTimeZone() (or an optional timezone argument) in the final SELECT for display, not in storage.
3. Aggregate in UTC, label locally. Do GROUP BY on UTC buckets, then convert the labels — this keeps DST transition days correct.
4. Set a server default explicitly. Configure <timezone>UTC</timezone> so behavior doesn't depend on the host OS.
5. Be deliberate with session_timezone. It silently re-interprets every un-zoned value in the session; prefer explicit per-column zones for multi-region consumers.
6. Pin/track tzdata. When supporting regions with shifting DST rules, verify TZDATA_VERSION after upgrades.

Common Issues

• "Times are off by N hours." The column has no timezone and the client/server defaults differ. Attach an explicit zone to the column or set session_timezone.
• Parse errors on insert. A string doesn't match the expected format or names an invalid offset; see Cannot parse DateTime and parseDateTimeBestEffort for lenient parsing.
• HTTP and native clients show different times. Conversion is happening in different places; wrap values in toTimeZone(ts, 'UTC') for a stable, interface-independent result.
• Daily aggregates look wrong on two days a year. Day buckets in a DST zone are 23/25 hours long — aggregate in UTC.

How Pulse Helps

Timezone bugs in ClickHouse are rarely loud — they surface as dashboards that are quietly off by an hour, day-boundary aggregations that drift twice a year, or HTTP and native clients disagreeing on the same query. Pulse monitors your ClickHouse deployment and reviews schema and query patterns, flagging un-zoned DateTime columns, server/session timezone mismatches across nodes, and outdated tzdata versions before they corrupt reporting. For teams running multi-region clusters, Pulse provides expert guidance on standardizing on UTC storage and correct conversion at query time, so time-based analytics stay accurate as your deployment grows.

Frequently Asked Questions

Q: Does ClickHouse store the timezone with each DateTime value?

No. Every DateTime/DateTime64 is stored as a timezone-agnostic Unix timestamp (UTC seconds). The timezone is type metadata that only controls text rendering and string parsing — it never changes the stored bytes.

Q: What's the difference between toTimeZone() and fromUTCTimestamp()?

toTimeZone(value, tz) keeps the same instant and re-labels its display zone. fromUTCTimestamp(value, tz) / toUTCTimestamp(value, tz) shift the wall-clock representation between a zone and UTC. Use toTimeZone for display; use the UTC-timestamp pair when you need Spark-style wall-clock conversion.

Q: How do I change the timezone for just my current session?

SET session_timezone = 'America/New_York';. This affects un-zoned columns and string parsing in that session only. Columns declared with an explicit zone (e.g. DateTime('UTC')) keep their own zone regardless.

Q: Why do the same timestamps display differently in clickhouse-client vs. the HTTP interface?

Conversion can happen client-side (native protocol) or server-side (HTTP, toString()). clickhouse-client defaults to the server timezone unless you pass --use_client_time_zone. Render with toTimeZone(ts, 'UTC') for an interface-independent result.

Q: How do I handle daylight saving time correctly?

Store and aggregate in UTC, then convert to local time only for display. This avoids non-existent spring-forward times, duplicate fall-back times, and 23/25-hour day buckets on transition days.

Q: How do I see which timezones ClickHouse supports?

Query SELECT * FROM system.time_zones; for the full list, and SELECT value FROM system.build_options WHERE name = 'TZDATA_VERSION'; to check the embedded tzdata version.