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
| Topic | The rule | Common mistake |
|---|---|---|
| Storage | DateTime / DateTime64 store a Unix timestamp. The timezone is column metadata only | Thinking DateTime('Europe/Amsterdam') stores "Amsterdam time" |
| Default timezone | Column timezone → session_timezone → server timezone → OS timezone at server start | Leaving it to whatever the server or container was started with |
String in WHERE / INSERT | Parsed in the column's timezone (or the default for plain DateTime) | Assuming toDateTime('...') behaves the same way |
toDateTime('...') with one argument | Parsed in the default timezone, never the column's | Filters that silently return 0 rows |
| Calendar functions | toDate, toStartOfDay, toHour, today() depend on a timezone | GROUP BY toDate(ts) on a UTC server for local reports |
| DST | Some local times don't exist, some happen twice; some days have 23 or 25 hours | Doing arithmetic on local wall-clock time |
| Best practice | Store in UTC, pass timezones explicitly, convert at the edges | Relying 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:
- Parsing a string into a timestamp (
'2026-11-09 08:00:00'→ seconds). - Formatting a timestamp back into text.
- Calendar calculations such as
toDate,toHour,toStartOfDayandtoStartOfMonth.
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:
| Type | Range | Precision |
|---|---|---|
DateTime | 1970-01-01 00:00:00 to 2106-02-07 06:28:15 | 1 second |
DateTime64(p) | 1900-01-01 to 2299-12-31 | up to nanoseconds (p = 0–9) |
Date / Date32 | Calendar date only | No 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:
- The column type's timezone, if there is one:
DateTime('UTC'). - The
session_timezonesetting, if it's set. - The server's
timezonesetting from the configuration. - 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 thingSingle 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_timezonedoesn't only change how plainDateTimevalues are displayed. It also changes how strings are parsed into plainDateTimecolumns. Setting it on a user that writes data changes what gets stored. Use it for read-only users, or don't use plainDateTimecolumns 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.
| Expression | Plain DateTime | DateTime('UTC') | DateTime('Europe/Amsterdam') |
|---|---|---|---|
Bare string in WHERE or INSERT | Default timezone | UTC | Europe/Amsterdam |
toDateTime('...') | Default timezone | Default timezone | Default timezone |
toDateTime('...', 'TZ') | TZ | TZ | TZ |
"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; -- 1For 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 CSVCheck 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 IstanbulThe 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 ALLbranch) 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;
-- 25If 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 HOURalways 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
| # | Step | Done when |
|---|---|---|
| 1 | Set <timezone>UTC</timezone> explicitly on every server | serverTimeZone() is identical across the cluster |
| 2 | Use DateTime('UTC') / DateTime64(p, 'UTC') for event time | The system.columns query returns nothing for new tables |
| 3 | Ingest Unix timestamps or ISO 8601 with offsets | No naked local strings in the pipeline |
| 4 | Always call toDateTime('...', 'TZ') with two arguments | Code review rejects one-argument toDateTime on strings |
| 5 | Pass the timezone to every calendar function in reports | No toDate(ts) without a timezone in dashboards |
| 6 | Filter local days with UTC ranges computed from local boundaries | Day filters compare the raw column with constants |
| 7 | Store users' IANA timezone names, not offsets | tenants.timezone holds names like Europe/Amsterdam |
| 8 | Pre-aggregate in UTC hours (or 15 minutes) | Any timezone's daily report can be derived from the MV |
| 9 | Use session_timezone only for read-only users | Writers never parse strings in a session timezone |
| 10 | Test with values near midnight and around DST changes | Reports match the external source on 25 October and 29 March |
TL;DR
DateTimestores 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→ servertimezone→ 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_effortparsing. - "Day" depends on the timezone. Pass one to
toDate,toStartOfDayand 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
DateTimeto 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
- ClickHouse docs: DateTime data type
- ClickHouse docs:
session_timezoneand other session settings - ClickHouse docs:
SETstatement andSET TIME ZONE - ClickHouse docs: Functions for working with dates and times
- ClickHouse docs:
date_time_input_format - ClickHouse blog: ClickHouse release 26.10
Further reading
From this blog



