All posts
ClickHouse Timezones Explained - Storing, Querying and Reporting Time Correctly

ClickHouse Timezones Explained - Storing, Querying and Reporting Time Correctly

November 9, 202619 min readCan Sayin
Share on social:

ClickHouse Timezones Explained: Storing, Querying and Reporting Time Correctly

It's Monday morning in Amsterdam. Finance opens the "Daily revenue" dashboard and compares Sunday's number with the payment provider's report. ClickHouse says €412,300. The provider says €428,900. Nobody lost money and no rows are missing. The dashboard simply counted Sunday from midnight UTC, while the provider counted it from midnight Amsterdam time. One hour of Saturday-night orders landed on the wrong day, and Sunday night's last hour went to Monday.

Timezone bugs rarely throw errors. The SQL is valid, the data is correct, and the result is still wrong. In ClickHouse they almost always come from one question nobody asked:

Which timezone did ClickHouse use to parse, display or truncate this value?

This post answers that question systematically. We'll use a fictional European marketplace, Tulipa Commerce, with customers in Amsterdam, Istanbul and New York, and go from the storage model to reporting, daylight saving time, schema design and troubleshooting.

Versions covered: the behavior described here is stable across recent ClickHouse versions. Examples were written against 26.x (26.10 is the latest release at the time of writing). Check the docs for your version before relying on edge cases.


At a glance

TopicThe ruleCommon mistake
StorageDateTime / DateTime64 store a Unix timestamp. The timezone is column metadata onlyThinking DateTime('Europe/Amsterdam') stores "Amsterdam time"
Default timezoneColumn timezone → session_timezone → server timezone → OS timezone at server startLeaving it to whatever the server or container was started with
String in WHERE / INSERTParsed in the column's timezone (or the default for plain DateTime)Assuming toDateTime('...') behaves the same way
toDateTime('...') with one argumentParsed in the default timezone, never the column'sFilters that silently return 0 rows
Calendar functionstoDate, toStartOfDay, toHour, today() depend on a timezoneGROUP BY toDate(ts) on a UTC server for local reports
DSTSome local times don't exist, some happen twice; some days have 23 or 25 hoursDoing arithmetic on local wall-clock time
Best practiceStore in UTC, pass timezones explicitly, convert at the edgesRelying on server, session or client defaults

1. The core rule: a DateTime is an instant, not a timezone

Internally, a ClickHouse DateTime is the number of seconds since 1970-01-01 00:00:00 UTC. DateTime64(p) is the same idea with sub-second precision. Neither stores a timezone in the row.

The timezone you write in the type, such as DateTime('UTC') or DateTime64(3, 'Europe/Amsterdam'), lives in the column metadata. ClickHouse uses it in exactly three situations:

  1. Parsing a string into a timestamp ('2026-11-09 08:00:00' → seconds).
  2. Formatting a timestamp back into text.
  3. Calendar calculations such as toDate, toHour, toStartOfDay and toStartOfMonth.

Everything else, including comparisons, sorting, min/max and subtraction, works on the raw number. Two columns with different timezones can hold exactly the same instant:

SELECT
    toDateTime('2026-11-09 08:00:00', 'UTC')    AS ts_utc,
    toTimeZone(ts_utc, 'Europe/Amsterdam')       AS ts_ams,
    toTimeZone(ts_utc, 'Europe/Istanbul')        AS ts_ist,
    toTimeZone(ts_utc, 'America/New_York')       AS ts_nyc,
    toUnixTimestamp(ts_utc) = toUnixTimestamp(ts_nyc) AS same_instant;
┌──────────────ts_utc─┬──────────────ts_ams─┬──────────────ts_ist─┬──────────────ts_nyc─┬─same_instant─┐
│ 2026-11-09 08:00:00 │ 2026-11-09 09:00:00 │ 2026-11-09 11:00:00 │ 2026-11-09 03:00:00 │            1 │
└─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┴──────────────┘

Because only the metadata changes, toTimeZone() is free: it doesn't touch the stored value at all.

Two limits worth remembering:

TypeRangePrecision
DateTime1970-01-01 00:00:00 to 2106-02-07 06:28:151 second
DateTime64(p)1900-01-01 to 2299-12-31up to nanoseconds (p = 0–9)
Date / Date32Calendar date onlyNo timezone at all

Date has no timezone. That sounds harmless until you realize that converting a DateTime into a Date needs one. That's where most reporting bugs start (section 5).


2. Where the "default timezone" comes from

When a value or column has no explicit timezone, ClickHouse uses the default timezone, resolved in this order:

  1. The column type's timezone, if there is one: DateTime('UTC').
  2. The session_timezone setting, if it's set.
  3. The server's timezone setting from the configuration.
  4. The operating system timezone at the moment the server started.

You can see the current state with two functions:

SELECT
    timeZone()       AS default_timezone,   -- session_timezone if set, otherwise the server timezone
    serverTimeZone() AS server_timezone;

Setting it at each level

Server (config.d/timezone.xml). Set it explicitly instead of inheriting it from the host or container image:

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

User or profile. Useful for a BI tool that should always see local time:

ALTER USER finance_bi SETTINGS session_timezone = 'Europe/Amsterdam';

Session. SET TIME ZONE is an alias added for PostgreSQL compatibility, which many drivers and ORMs send automatically on connect:

SET session_timezone = 'Europe/Amsterdam';
SET TIME ZONE 'Europe/Amsterdam';   -- same thing

Single query:

SELECT now() SETTINGS session_timezone = 'Europe/Istanbul';

HTTP interface. Like any setting, it can be passed as a URL parameter:

curl 'http://localhost:8123/?session_timezone=America/New_York' \
     --data-binary 'SELECT now(), timeZone()'

clickhouse-client uses the server timezone by default. Add --use_client_time_zone to use your laptop's timezone instead.

Watch out: session_timezone doesn't only change how plain DateTime values are displayed. It also changes how strings are parsed into plain DateTime columns. Setting it on a user that writes data changes what gets stored. Use it for read-only users, or don't use plain DateTime columns at all (section 7).

Valid names are the ones listed in system.time_zones. ClickHouse ships its own copy of the IANA timezone database, so results don't depend on the host's tzdata. A fixed offset such as Fixed/UTC+05:30:00 is also accepted.


3. The trap: a string literal and toDateTime('...') are not the same

This one causes more "the filter returns nothing" tickets than anything else. Let's create Tulipa's orders table with three timestamp columns:

CREATE TABLE orders_demo
(
    order_id   UInt64,
    created    DateTime,                       -- plain, uses the default timezone
    created_utc DateTime('UTC'),
    created_ams DateTime('Europe/Amsterdam')
)
ENGINE = MergeTree
ORDER BY order_id;
 
-- One order placed at 08:00 UTC = 09:00 in Amsterdam (server timezone: UTC)
INSERT INTO orders_demo VALUES
(1, '2026-11-09 08:00:00', '2026-11-09 08:00:00', '2026-11-09 09:00:00');

All three columns now store the same instant. Now filter the Amsterdam column using the value it displays:

-- ✅ Returns the row: the string is parsed in the column's timezone (Amsterdam)
SELECT order_id FROM orders_demo
WHERE created_ams = '2026-11-09 09:00:00';
 
-- ❌ Returns 0 rows: toDateTime() is evaluated first, in the DEFAULT timezone (UTC),
--    so this means 09:00 UTC, one hour after the stored instant
SELECT order_id FROM orders_demo
WHERE created_ams = toDateTime('2026-11-09 09:00:00');
 
-- ✅ Returns the row: the timezone is explicit
SELECT order_id FROM orders_demo
WHERE created_ams = toDateTime('2026-11-09 09:00:00', 'Europe/Amsterdam');

The difference is when the string becomes a timestamp:

  • A bare string compared with a column is converted using that column's type, so it inherits the column's timezone.
  • toDateTime('...') is evaluated on its own, before the comparison. It can't see the column, so it uses the default timezone.

The same rule applies to INSERT. A bare string inserted into DateTime('Europe/Amsterdam') is read as Amsterdam time. toDateTime('...') inserted into the same column is read in the default timezone and only then stored.

ExpressionPlain DateTimeDateTime('UTC')DateTime('Europe/Amsterdam')
Bare string in WHERE or INSERTDefault timezoneUTCEurope/Amsterdam
toDateTime('...')Default timezoneDefault timezoneDefault timezone
toDateTime('...', 'TZ')TZTZTZ

"Default timezone" means session_timezone if set, otherwise the server timezone. The only form with no hidden dependency is the last row. Always pass the timezone explicitly when you turn a string into a timestamp in SQL.


4. Getting data in without guessing

Most timezone bugs are created at ingestion, not at query time. The safest inputs are the ones that already identify an instant.

Prefer Unix timestamps or ISO 8601 with an offset

A number inserted into a DateTime column is always a Unix timestamp, so it's always UTC, whatever the column, session or server timezone:

-- 1794211200 = 2026-11-09 08:00:00 UTC
INSERT INTO orders_demo (order_id, created_utc) VALUES (2, 1794211200);

Strings with an explicit offset or Z also identify an instant. Parse them with the best-effort parser, which honors the offset:

SELECT
    parseDateTimeBestEffort('2026-11-09T08:00:00Z')        AS from_z,
    parseDateTimeBestEffort('2026-11-09T09:00:00+01:00')   AS from_offset,
    from_z = from_offset                                   AS same_instant;   -- 1

For input formats like JSONEachRow or CSV, set date_time_input_format = 'best_effort' so that ISO 8601 values with offsets are accepted directly. The default (basic) only accepts the simple YYYY-MM-DD hh:mm:ss form and the numeric Unix form.

Avoid "naked" local strings

A string like '2026-11-09 09:00:00' doesn't say which timezone it belongs to. ClickHouse will pick one using the rules in section 3, and the result depends on the column type and the session. If an upstream system only produces naked local strings, convert them once, at the edge, with an explicit zone:

INSERT INTO orders (order_id, created_at)
SELECT order_id, toDateTime(local_ts, 'Europe/Istanbul')
FROM input('order_id UInt64, local_ts String')
FORMAT CSV

Check what drivers send

Many client libraries convert native date objects to strings or numbers before sending them. Some use the client machine's timezone, some use UTC, some use the session timezone. Test your driver with a known instant before trusting it in production. A five-minute test with a value near midnight saves a lot of debugging later.


5. Reporting by local day: where the money goes missing

Back to the finance dashboard. Here are three orders from Tulipa's Saturday night:

CREATE TABLE orders
(
    order_id    UInt64,
    tenant_id   UInt32,
    country     LowCardinality(String),
    created_at  DateTime('UTC'),
    amount_eur  Decimal(18, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(created_at)
ORDER BY (tenant_id, created_at);
 
INSERT INTO orders VALUES
(101, 1, 'NL', '2026-11-07 22:40:00', 120.00),   -- 23:40 Saturday in Amsterdam
(102, 1, 'NL', '2026-11-07 23:20:00',  85.50),   -- 00:20 Sunday in Amsterdam
(103, 1, 'TR', '2026-11-07 21:30:00',  64.00);   -- 00:30 Sunday in Istanbul

The naive daily report:

SELECT toDate(created_at) AS day, sum(amount_eur) AS revenue
FROM orders
GROUP BY day;

Every order lands on 2026-11-07, because toDate() on a DateTime('UTC') column truncates at midnight UTC. For an Amsterdam customer, order 102 happened on Sunday. For an Istanbul customer, so did order 103.

Every calendar function accepts an optional timezone argument, and that's how you fix it:

SELECT
    order_id,
    toDate(created_at)                       AS day_utc,
    toDate(created_at, 'Europe/Amsterdam')   AS day_ams,
    toDate(created_at, 'Europe/Istanbul')    AS day_ist,
    toDate(created_at, 'America/New_York')   AS day_nyc
FROM orders
ORDER BY order_id;
┌─order_id─┬────day_utc─┬────day_ams─┬────day_ist─┬────day_nyc─┐
│      101 │ 2026-11-07 │ 2026-11-07 │ 2026-11-08 │ 2026-11-07 │
│      102 │ 2026-11-07 │ 2026-11-08 │ 2026-11-08 │ 2026-11-07 │
│      103 │ 2026-11-07 │ 2026-11-07 │ 2026-11-08 │ 2026-11-07 │
└──────────┴────────────┴────────────┴────────────┴────────────┘

Same rows, four different "days". None of them is wrong; they answer different questions. The bug is not choosing one on purpose.

The functions that depend on a timezone include toDate, toStartOfDay, toStartOfHour (for zones with non-whole-hour offsets), toStartOfWeek, toStartOfMonth, toStartOfInterval, dateTrunc, toHour, toDayOfWeek, toYYYYMM and formatDateTime. So do today() and yesterday(): they return the date in the default timezone.

Filter with ranges, not functions on the column

For "orders on Sunday, Amsterdam time", compute the boundaries and filter the raw column:

SELECT count(), sum(amount_eur)
FROM orders
WHERE tenant_id = 1
  AND created_at >= toDateTime('2026-11-08 00:00:00', 'Europe/Amsterdam')
  AND created_at <  toDateTime('2026-11-09 00:00:00', 'Europe/Amsterdam');

This is equivalent to toDate(created_at, 'Europe/Amsterdam') = '2026-11-08', but it compares the column directly with two constants. The primary key, partition pruning and skip indexes work in the simplest possible way, and the query is unambiguous to anyone reading it. ClickHouse can often analyze monotonic functions like toDate in index analysis too, but a range on the raw column never depends on that.

One timezone per query

In most date and time functions, the timezone argument must be a constant. You can't pass a column such as tenants.timezone and get a different zone per row. For a multi-tenant product, the usual pattern is:

  • Per-tenant dashboards: the application knows the tenant's timezone and passes it as a query parameter. Parameters are substituted as constants:

    SELECT
        toStartOfDay(created_at, {tz:String}) AS local_day,
        sum(amount_eur)                       AS revenue
    FROM orders
    WHERE tenant_id = {tenant:UInt32}
      AND created_at >= {from:DateTime('UTC')}
      AND created_at <  {to:DateTime('UTC')}
    GROUP BY local_day
    ORDER BY local_day;
  • Cross-tenant reports: group tenants by timezone in the application and run one query (or one UNION ALL branch) per distinct timezone. Most products have far fewer distinct timezones than tenants.


6. Daylight saving time: gaps, overlaps and 25-hour days

UTC never changes. Local time does. In zones with daylight saving time (DST), the local clock jumps twice a year, and three things follow from that.

Some local times don't exist

On 29 March 2026, Amsterdam clocks jump from 02:00 to 03:00. The local time 02:30 never happened. ClickHouse won't throw an error if you parse it; it silently maps it to a real instant. The ClickHouse docs show this for London, where a non-existent 01:30 is shifted back to 00:30. Either way, the value you get isn't the one you wrote.

Some local times happen twice

On 25 October 2026, Amsterdam clocks go back from 03:00 to 02:00, so 02:30 happens twice, one hour apart:

SELECT
    toDateTime('2026-10-25 00:30:00', 'UTC')::DateTime('Europe/Amsterdam') AS first_0230,
    toDateTime('2026-10-25 01:30:00', 'UTC')::DateTime('Europe/Amsterdam') AS second_0230;
┌──────────first_0230─┬─────────second_0230─┐
│ 2026-10-25 02:30:00 │ 2026-10-25 02:30:00 │
└─────────────────────┴─────────────────────┘

Two different instants, identical text. When parsing an ambiguous local string, ClickHouse picks the earlier occurrence. If you store local strings, you lose the information needed to tell these apart.

Some days aren't 24 hours

SELECT
    (toUnixTimestamp(toDateTime('2026-10-26 00:00:00', 'Europe/Amsterdam'))
   - toUnixTimestamp(toDateTime('2026-10-25 00:00:00', 'Europe/Amsterdam'))) / 3600 AS hours_in_day;
-- 25

If your dashboard shows "average orders per hour" per local day, 25 October has 25 hours and 29 March has 23. Dividing by a hard-coded 24 makes both days look wrong. Hourly charts in local time will also show either a missing bar or two bars with the same label on those days.

What to do about it

  • Store and compute in UTC. UTC has no gaps or overlaps, so arithmetic like created_at + INTERVAL 1 HOUR always means exactly one hour.
  • Convert to local time only for display and calendar grouping, with an explicit timezone.
  • Never store local wall-clock strings as the source of truth for event time.
  • Zones without DST (Istanbul has used a fixed UTC+3 since 2016) are easier, but rules change over time. ClickHouse ships the tz database with the server, so keep ClickHouse reasonably up to date when governments change their rules.

7. Schema design for multi-region products

Putting it all together, these are the patterns we recommend for Tulipa and for most real systems.

Make UTC explicit in the column type

CREATE TABLE events
(
    tenant_id   UInt32,
    event_time  DateTime64(3, 'UTC'),
    event_type  LowCardinality(String),
    user_id     UInt64,
    amount_eur  Decimal(18, 2)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (tenant_id, event_type, event_time);

DateTime('UTC') behaves the same on every server, in every session, for every client. Plain DateTime changes meaning when someone changes the server config or sets session_timezone. Even if your server runs in UTC today, writing it in the type makes the intent visible and survives migrations, new replicas and new tools.

Keep the user's timezone as data

Store the IANA name, not an offset. An offset like +01:00 is only correct for part of the year.

CREATE TABLE tenants
(
    tenant_id  UInt32,
    name       String,
    timezone   LowCardinality(String)   -- 'Europe/Amsterdam', 'Europe/Istanbul', ...
)
ENGINE = ReplacingMergeTree
ORDER BY tenant_id;

Load it into a dictionary if many queries need it, and let the application pass it as a parameter, as in section 5.

Partitions are UTC, and that's fine

PARTITION BY toYYYYMM(event_time) on a UTC column splits data at UTC month boundaries. A local month for Amsterdam will touch two partitions for its first and last hour. Partitions are a data-management tool, not a reporting boundary, so this costs almost nothing. Don't create separate tables or partition keys per timezone.

Pre-aggregate in UTC, at a granularity every timezone can use

Materialized views are where a hidden timezone becomes permanent. If an MV groups by toDate(event_time), it bakes UTC days into the aggregate, and no Amsterdam or New York daily report can be derived from it later.

Aggregate by UTC hour instead, and roll up to local days at query time:

CREATE TABLE revenue_hourly
(
    tenant_id   UInt32,
    hour_utc    DateTime('UTC'),
    orders      AggregateFunction(count),
    revenue     AggregateFunction(sum, Decimal(18, 2))
)
ENGINE = AggregatingMergeTree
ORDER BY (tenant_id, hour_utc);
 
CREATE MATERIALIZED VIEW revenue_hourly_mv TO revenue_hourly AS
SELECT
    tenant_id,
    toStartOfHour(event_time) AS hour_utc,
    countState()              AS orders,
    sumState(amount_eur)      AS revenue
FROM events
WHERE event_type = 'purchase'
GROUP BY tenant_id, hour_utc;
 
-- Local daily revenue for any whole-hour timezone
SELECT
    toDate(hour_utc, {tz:String}) AS local_day,
    countMerge(orders)            AS orders,
    sumMerge(revenue)             AS revenue
FROM revenue_hourly
WHERE tenant_id = {tenant:UInt32}
GROUP BY local_day
ORDER BY local_day;

Hourly buckets work for every timezone with a whole-hour offset. If you serve customers in India (UTC+5:30) or Nepal (UTC+5:45), use 15-minute buckets (toStartOfFifteenMinutes) so that every local midnight falls exactly on a bucket boundary.

Pass explicit time boundaries from the application

As we described in our high-concurrency tuning guide, queries with now() or today() aren't cached by the query result cache by default. Computing boundaries in the application, in UTC, and passing them as parameters fixes two things at once: the query is cacheable, and it no longer depends on the session's default timezone. (ClickHouse 26.10 also lets the query condition cache handle now() and today() filters by snapping them to a time grid, which helps for filters you can't rewrite.)


8. Finding timezone bugs before your users do

Do all servers agree?

SELECT hostName(), serverTimeZone(), timeZone()
FROM clusterAllReplicas('default', system.one);

A replica that was rebuilt from a different image with a different OS timezone will display and parse plain DateTime values differently from its neighbors. With distributed queries, the result can then depend on which replica answered.

Which columns have no explicit timezone?

SELECT database, table, name, type
FROM system.columns
WHERE match(type, '^(Nullable\\()?DateTime(64\\(\\d\\))?\\)?$')
  AND database NOT IN ('system', 'INFORMATION_SCHEMA', 'information_schema')
ORDER BY database, table, name;

This lists DateTime, DateTime64(p) and their Nullable variants without a timezone argument. Each one is a column whose meaning depends on server and session settings.

Which users or profiles change the timezone?

SELECT name, value, changed
FROM system.settings
WHERE name = 'session_timezone';

Run it as each application user. Also review CREATE USER and settings profile definitions for session_timezone, especially for users that insert data.

A quick test with a midnight value

When a report looks off, insert or pick an event at 23:30 UTC and check which day each layer assigns it to: the raw table, every MV, the BI tool and the export. The layer where it changes day is where the hidden timezone lives.


Checklist

#StepDone when
1Set <timezone>UTC</timezone> explicitly on every serverserverTimeZone() is identical across the cluster
2Use DateTime('UTC') / DateTime64(p, 'UTC') for event timeThe system.columns query returns nothing for new tables
3Ingest Unix timestamps or ISO 8601 with offsetsNo naked local strings in the pipeline
4Always call toDateTime('...', 'TZ') with two argumentsCode review rejects one-argument toDateTime on strings
5Pass the timezone to every calendar function in reportsNo toDate(ts) without a timezone in dashboards
6Filter local days with UTC ranges computed from local boundariesDay filters compare the raw column with constants
7Store users' IANA timezone names, not offsetstenants.timezone holds names like Europe/Amsterdam
8Pre-aggregate in UTC hours (or 15 minutes)Any timezone's daily report can be derived from the MV
9Use session_timezone only for read-only usersWriters never parse strings in a session timezone
10Test with values near midnight and around DST changesReports match the external source on 25 October and 29 March

TL;DR

  • DateTime stores an instant. The timezone in the type is metadata used for parsing, display and calendar math. toTimeZone() is free.
  • Know your default timezone: column → session_timezone → server timezone → OS. Set the server to UTC explicitly.
  • Bare strings use the column's timezone; toDateTime('...') uses the default one. Always pass the timezone as the second argument.
  • Ingest instants, not local strings: Unix timestamps, or ISO 8601 with an offset and best_effort parsing.
  • "Day" depends on the timezone. Pass one to toDate, toStartOfDay and friends, and filter local days with UTC ranges.
  • DST creates missing hours, repeated hours and 25-hour days. Compute in UTC, convert only at the edges.
  • Design for many timezones: UTC columns, IANA names as data, MVs aggregated by UTC hour or 15 minutes.

How BlancoByte can help

At BlancoByte we help teams design and run ClickHouse for analytics products, observability and real-time reporting, both self-managed and on ClickHouse Cloud. Timezone issues usually show up as "the numbers don't match", and they're often spread across ingestion, schemas, materialized views and BI tools at once. We can:

  • Audit your schema and pipelines for un-zoned columns, hidden session settings and timezone-dependent materialized views.
  • Redesign reporting layers so that every customer gets correct local days without duplicating data.
  • Plan safe migrations from plain DateTime to explicit UTC columns, including backfills of existing aggregates.
  • Review your multi-region setup, from server configuration to drivers and dashboards.

If your daily numbers never quite match the source, get in touch. We're happy to start with a free review of your setup.


References

ClickHouse

Further reading

From this blog

Need help with your data platform?

BlancoByte designs and runs real-time pipelines and modern data infrastructure. We work alongside your team, from architecture to production.

Share on social: