The Hidden Timezone Rules of DateTime in ClickHouse®

A DateTime value in ClickHouse can look deceptively simple: insert a date and time value, query it later, and you expect to get the same date and time value back. But sooner rather than later, many users hit a confusing case: a filter that should match a row returns nothing, even though the date and time value in the query looks exactly like the date and time value you’ve inserted into the table. The SQL is valid. The stored value did not change. What most likely changed is the timezone ClickHouse used to parse or display that value.

The root of the problem is that DateTime does not store a timezone. It stores a point in time as a number and this number represents the number of seconds since the Unix epoch. Timezones only appear at the boundaries: when ClickHouse turns a string such as '2024-01-15 12:00:00' into a point in time, and when it turns the stored value back into text. Those boundaries are where most surprises happen.

In the examples below, we will use one table with several DateTime columns: a plain DateTime, plus timezone-qualified columns such as DateTime('UTC'), DateTime('Europe/Berlin'), and DateTime('America/New_York').

Using this table, we will compare what happens when the same date and time appears in different places: in an INSERT, in a WHERE clause, and inside toDateTime(...). We will also see how the session_timezone setting changes the result, and what happens around Daylight Saving Time transitions.

By the end, you should be able to answer the question that prevents most date and time bugs in ClickHouse:

Which timezone did ClickHouse use when it parsed or displayed a DateTime value?

Once you can answer that, you’ll see that ClickHouse follows just a small set of rules and all the perceived inconsistencies related to date and time disappear.

The Core Rule: DateTime Stores an Instant, Not a Timezone

The first step to answering that question is to separate two ideas that are easy to mix together: the stored value and the way that value is shown to a human.

As mentioned above, internally, a ClickHouse DateTime value is stored as Unix seconds: the number of seconds since the Unix epoch, 1970-01-01 00:00:00 UTC. That stored number does not carry a timezone label. For example, the Unix timestamp 1705320000 represents the instant 2024-01-15 12:00:00 UTC. The same instant is 13:00 in Berlin, 07:00 in New York, and 21:00 in Tokyo. Nothing about the value on disk changes between those views. Only the timezone-driven wall-clock representation changes.

ClickHouse exposes this distinction through how the column type is specified. You can declare column datatypes of DateTime, DateTime('UTC'), DateTime('Europe/Berlin'), and so on. These types all store Unix seconds. The timezone in the type does not change the physical value being stored. Instead, it tells ClickHouse which timezone to use when it crosses the boundary between strings and Unix seconds.

This appears in two common places: when ClickHouse parses a string into a timestamp, and when it formats a stored timestamp back into text. A plain DateTime column uses the server timezone by default, or the session timezone when the session_timezone setting is set. In contrast, columns such as DateTime('UTC') or DateTime('Europe/Berlin') specify the timezone explicitly, so ClickHouse uses that explicit timezone when interpreting or displaying the values for those columns.

And, this is why two columns can store the same instant but print different clock times. It is also why we need to focus on the most important question: which timezone did ClickHouse use when it parsed or displayed the DateTime value?

When ClickHouse parses or displays a DateTime value, it needs a timezone. Here’s how it finds or creates one: 

• If the column’s datatype includes a timezone (DateTime('Europe/Berlin'), for example), ClickHouse uses it.
• If the value or the column’s datatype does not include a timezone, ClickHouse uses the default timezone. The default timezone is the session_timezone setting if it is defined; otherwise ClickHouse uses the server’s timezone setting.

The timeZone() function returns the default timezone, while the serverTimeZone() function returns the server’s timezone setting:

SELECT
timeZone() AS default_timezone,
serverTimeZone() AS server_timezone;

   ┌─default_timezone─┬─server_timezone─┐
1. │ Asia/Tokyo       │ UTC             │
   └──────────────────┴─────────────────┘

For the rest of the article, we’ll simply say “the default timezone” instead of repeating the rules for how ClickHouse picks a timezone. 

DateTime64 Follows the Same Rules As DateTime

Before we start looking at the examples, we need to note that the same mental model that we will develop for DateTime applies to DateTime64. It also stores a point in time, not a timezone. The difference between the two is just precision: DateTime stores whole seconds, while DateTime64 can store fractional seconds.

Just like DateTime, DateTime64 can also have an optional timezone, for example DateTime64(3), DateTime64(3, 'UTC'), or DateTime64(3, 'Europe/Berlin'). When specified, the timezone affects how strings are parsed and how values are displayed, just like with DateTime.

So when you see an example like:

toDateTime('2024-01-15 12:00:00', 'UTC')

the DateTime64 version is:

toDateTime64('2024-01-15 12:00:00.123', 3, 'UTC')

Everything else in the article carries over, so we will not be providing DateTime64 examples to keep it short.

A Demo Table: One Instant, Four Timezones

Now let’s see how it all works and make the core rule visible by storing one point in time — 2024-01-15 12:00:00 UTC — in four DateTime columns:

• dt_plain – DateTime
• dt_utc – DateTime('UTC')
• dt_berlin – DateTime('Europe/Berlin')
• dt_ny – DateTime('America/New_York')

We’ll run our examples on ClickHouse 26.5. The behavior is stable across recent versions, but if you use an older build, the exact output formatting or some session_timezone setting edge cases may differ.

The examples also assume the server timezone is UTC. You can check that with:

SELECT serverTimeZone(), timeZone();

The output should be:

   ┌─serverTimeZone()─┬─timeZone()─┐
1. │ UTC              │ UTC        │
   └──────────────────┴────────────┘

First, let’s create the demo table:

DROP TABLE IF EXISTS datetime_tz_demo;
CREATE TABLE datetime_tz_demo
(
    id         UInt8,
    event_name String,
    dt_plain   DateTime,
    dt_utc     DateTime('UTC'),
    dt_berlin  DateTime('Europe/Berlin'),
    dt_ny      DateTime('America/New_York')
) 
ENGINE = MergeTree 
ORDER BY id;

Now insert the same instant into every column:

INSERT INTO datetime_tz_demo
SELECT
    1,
    'Product launch',
    toDateTime('2024-01-15 12:00:00', 'UTC'),
    toDateTime('2024-01-15 12:00:00', 'UTC'),
    toDateTime('2024-01-15 12:00:00', 'UTC'),
    toDateTime('2024-01-15 12:00:00', 'UTC');

Then read the inserted row back:

SELECT dt_plain, dt_utc, dt_berlin, dt_ny
FROM datetime_tz_demo;

ClickHouse prints the same stored instant differently depending on the column type:

   ┌────────────dt_plain─┬──────────────dt_utc─┬───────────dt_berlin─┬───────────────dt_ny─┐
1. │ 2024-01-15 12:00:00 │ 2024-01-15 12:00:00 │ 2024-01-15 13:00:00 │ 2024-01-15 07:00:00 │
   └─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┘

All four columns store the same point in time, but each column uses its own rule when formatting that value back into text. dt_plain is plain DateTime, so ClickHouse uses the server timezone by default. In this example, the server timezone is UTC, so it prints as 2024-01-15 12:00:00. This can change when session_timezone is set; we will look at that behavior in a later section.

The explicit-timezone columns use the timezone written in their type. dt_utc is DateTime('UTC'), so it also prints as 2024-01-15 12:00:00. dt_berlin is DateTime('Europe/Berlin'); Berlin is one hour ahead of UTC on January 15, so the same instant prints as 2024-01-15 13:00:00. dt_ny is DateTime('America/New_York'); New York is five hours behind UTC on January 15, so the same instant prints as 2024-01-15 07:00:00.

The main point is that the stored value is the same in every column, but the display rule is different.

The Trap: A Date String and toDateTime(...) Are Not the Same

The previous section showed how the same stored instant can be displayed differently depending on the column timezone. Now let’s use one of those displayed values in a filter.

The Berlin column, dt_berlin, prints the stored instant as 2024-01-15 13:00:00:

SELECT id, event_name, dt_berlin 
FROM datetime_tz_demo;

   ┌─id─┬─event_name─────┬───────────dt_berlin─┐
1. │  1 │ Product launch │ 2024-01-15 13:00:00 │
   └────┴────────────────┴─────────────────────┘

So this filter looks natural:

SELECT id, event_name, dt_berlin
FROM datetime_tz_demo
WHERE dt_berlin = '2024-01-15 13:00:00';

And this query returns this row:

   ┌─id─┬─event_name─────┬───────────dt_berlin─┐
1. │  1 │ Product launch │ 2024-01-15 13:00:00 │
   └────┴────────────────┴─────────────────────┘

In this comparison, ClickHouse has to turn the string '2024-01-15 13:00:00' into a DateTime value before it can compare it with dt_berlin. Because dt_berlin is DateTime('Europe/Berlin'), ClickHouse interprets the string in Berlin time. So the filter means: find rows where dt_berlin represents 2024-01-15 13:00:00 in Berlin.

On January 15, 13:00 in Berlin is the same instant as 12:00 UTC — the instant stored in the row. That is why the row matches.

Now let’s make a small change to the query. Instead of writing the date string directly in the comparison, let’s pass the same string to the toDateTime(...) function:

SELECT id, event_name, dt_berlin
FROM datetime_tz_demo
WHERE dt_berlin = toDateTime('2024-01-15 13:00:00');

At first glance, this still looks like the same comparison. The column is the same, and the visible date and time are the same: 2024-01-15 13:00:00.

However, the result is different. This query returns no rows:

0 rows in set.

The reason is that while this string may look the same, ClickHouse parses it differently. A one-argument toDateTime('...') does not use the timezone of dt_berlin. It uses the default timezone. In our setup, the server timezone is UTC, so ClickHouse reads this value as 2024-01-15 13:00:00 UTC. But that is one hour later than the stored row – 2024-01-15 12:00:00 UTC! If we want toDateTime(...) to mean Berlin time, we need to say that explicitly:

SELECT id, event_name, dt_berlin
FROM datetime_tz_demo
WHERE dt_berlin = toDateTime('2024-01-15 13:00:00', 'Europe/Berlin');

Now the row matches again:

   ┌─id─┬─event_name─────┬───────────dt_berlin─┐
1. │  1 │ Product launch │ 2024-01-15 13:00:00 │
   └────┴────────────────┴─────────────────────┘

This is because this time, ClickHouse reads the string as 2024-01-15 13:00:00 in Berlin, which points to the stored instant.

We can also write the same instant in UTC:

SELECT id, event_name, dt_berlin
FROM datetime_tz_demo
WHERE dt_berlin = toDateTime('2024-01-15 12:00:00', 'UTC');

This query returns the same row too:

   ┌─id─┬─event_name─────┬───────────dt_berlin─┐
1. │  1 │ Product launch │ 2024-01-15 13:00:00 │
   └────┴────────────────┴─────────────────────┘

So far, it is the column itself that gives ClickHouse a timezone to use. Plain DateTime is different: the column type does not contain timezone information. So when ClickHouse sees a direct date string in a WHERE clause, it uses the default timezone to parse that string. 

Let’s see this behavior by using the dt_plain column instead of dt_berlin. In our setup, the server timezone is set to UTC. So when we execute this query:

SELECT id, event_name, dt_plain
FROM datetime_tz_demo
WHERE dt_plain = '2024-01-15 12:00:00';

it treats the string '2024-01-15 12:00:00' as a UTC time. That points to the same instant we inserted earlier, so the row matches and we get 1 row in output:

   ┌─id─┬─event_name─────┬────────────dt_plain─┐
1. │  1 │ Product launch │ 2024-01-15 12:00:00 │
   └────┴────────────────┴─────────────────────┘

Based on these two examples, we can now summarize the rules. A date string written directly in the query’s WHERE clause is interpreted through the column type to which the string is being compared against. If the column has an explicit timezone, ClickHouse uses that timezone. If the column is plain DateTime, ClickHouse uses the default timezone.

If you use the function toDateTime('...'), the behavior is different. It is evaluated on its own before the comparison, so it can’t inherit the timezone from the column to which it is being compared. Instead, ClickHouse uses the default timezone.

What Does the session_timezone Setting Change?

So far, we have seen only that plain DateTime uses UTC as its timezone because our server timezone is UTC. That made the examples look simple: a string like '2024-01-15 12:00:00' was read as 2024-01-15 12:00 UTC.

But ClickHouse lets you set the default timezone for the current session. For example, let us switch the session timezone to Tokyo:

SET session_timezone = 'Asia/Tokyo';

This changes the timezone returned by timeZone() function, but it does not change the server timezone:

SELECT timeZone(), serverTimeZone();

   ┌─timeZone()─┬─serverTimeZone()─┐
1. │ Asia/Tokyo │ UTC              │
   └────────────┴──────────────────┘

For now, the default timezone is Asia/Tokyo.

NOTE: If you’re running these examples in the Query Explorer of the Altinity Cloud Manager, each statement is executed in its own session. That means the settings you define in one statement don’t apply to the next one. You can get around this by using the SETTINGS clause in each of your SELECT and INSERT statements: 

SELECT dt_plain, dt_utc, dt_berlin, dt_ny
FROM datetime_tz_demo
SETTINGS session_timezone = 'Asia/Tokyo'; 

or

INSERT INTO datetime_tz_demo
    (id, event_name, dt_plain, dt_utc, dt_berlin, dt_ny)
SETTINGS session_timezone ='Asia/Tokyo'
VALUES (
    2,
    'Morning sync',
    '2024-01-15 12:00:00',
    '2024-01-15 12:00:00',
    '2024-01-15 13:00:00',
    '2024-01-15 07:00:00'
);

With the default timezone updated, let’s read the same row again and see which displayed values change:

SELECT dt_plain, dt_utc, dt_berlin, dt_ny
FROM datetime_tz_demo;

   ┌────────────dt_plain─┬──────────────dt_utc─┬───────────dt_berlin─┬───────────────dt_ny─┐
1. │ 2024-01-15 21:00:00 │ 2024-01-15 12:00:00 │ 2024-01-15 13:00:00 │ 2024-01-15 07:00:00 │
   └─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┘

We can see that only dt_plain changed. That is the important part. The dt_plain column is a plain DateTime, so again it does not have a timezone in the column type. When ClickHouse displays it, it still needs a timezone. Before, the server timezone was used. Now the session timezone is used, so the same stored instant prints as 2024-01-15 21:00:00.

We must remember that the session timezone only matters when ClickHouse needs a timezone to display or interpret the value when a column does not have an explicit timezone. This is why dt_utc still prints in UTC, dt_berlin still prints in Berlin time, and dt_ny still prints in New York time.

Now let’s see how a WHERE filter works with a plain DateTime value when session_timezone is set.

With the session timezone set to Asia/Tokyo, the row now prints as 2024-01-15 21:00:00 in dt_plain. If we use that displayed value in the filter, it matches our stored row:

SELECT id, event_name, dt_plain
FROM datetime_tz_demo
WHERE dt_plain = '2024-01-15 21:00:00';

   ┌─id─┬─event_name─────┬────────────dt_plain─┐
1. │  1 │ Product launch │ 2024-01-15 21:00:00 │
   └────┴────────────────┴─────────────────────┘

ClickHouse reads the string as 2024-01-15 21:00:00 in Tokyo. That is the same instant as 2024-01-15 12:00:00 UTC, which is the instant stored in the row.

But the old UTC-looking value no longer means the same thing in this session. For example, this query does not match the stored row:

SELECT id, event_name, dt_plain
FROM datetime_tz_demo
WHERE dt_plain = '2024-01-15 12:00:00';

0 rows in set.

It returns nothing, because ClickHouse now reads the string as 2024-01-15 12:00:00 in Tokyo. That is 2024-01-15 03:00:00 UTC; the stored value is still 2024-01-15 12:00:00 UTC, so these are different instants.

Now, let’s compare this against the column that already has a timezone in its type. In this case, the column timezone still wins. Even under the Tokyo session timezone. Here is the query that matches the stored row:

SELECT id, event_name, dt_utc
FROM datetime_tz_demo
WHERE dt_utc = '2024-01-15 12:00:00';

   ┌─id─┬─event_name─────┬──────────────dt_utc─┐
1. │  1 │ Product launch │ 2024-01-15 12:00:00 │
   └────┴────────────────┴─────────────────────┘

The dt_utc column is DateTime('UTC'), so ClickHouse reads the direct string as UTC. The session timezone does not change that.

The easy trap is to assume that toDateTime(...) behaves the same way. It does not!

A one-argument call to the toDateTime('...') function is evaluated before the comparison, so it needs a timezone to convert the value. That timezone will come from the default timezone, not the datatype of the dt_utc column. Therefore, when Tokyo is the default timezone, this query does not match the stored row:

SELECT id, event_name, dt_utc
FROM datetime_tz_demo
WHERE dt_utc = toDateTime('2024-01-15 12:00:00');

0 rows in set.

It returns no rows because toDateTime('2024-01-15 12:00:00') is parsed as 2024-01-15 12:00:00 in Tokyo, which is 2024-01-15 03:00:00 UTC, but the stored row is still 2024-01-15 12:00:00 UTC.

However, make the timezone explicit and the row matches again:

SELECT id, event_name, dt_utc
FROM datetime_tz_demo
WHERE dt_utc = toDateTime('2024-01-15 12:00:00', 'UTC');

   ┌─id─┬─event_name─────┬──────────────dt_utc─┐
1. │  1 │ Product launch │ 2024-01-15 12:00:00 │
   └────┴────────────────┴─────────────────────┘

The rule is simple: session_timezone changes the default timezone. It affects plain DateTime columns and one-argument toDateTime(...) function calls. However, it does not override the timezone of a column that already has one.

The Same Timezone Rules Apply to INSERT

Until now, we have used WHERE clauses to see how ClickHouse interprets date strings during filtering. But the same question appears when data is written into the table. When an INSERT contains a date string, ClickHouse has to turn that string into a stored instant. The question is the same: which timezone does ClickHouse use for that conversion? For INSERT, the answer depends on two things: how the value is written, and what type the target column has.

Before we start, reset the session timezone so we are back to the original setup:

SET session_timezone = '';

Double-check that we are back to UTC, and there is no active session timezone in effect:

SELECT serverTimeZone(), timeZone();

The output should be:

   ┌─serverTimeZone()─┬─timeZone()─┐
1. │ UTC              │ UTC        │
   └──────────────────┴────────────┘

Direct strings use the target column type

Let’s start with the simplest case: inserting date strings directly. In the query below, the four strings look different on purpose:

INSERT INTO datetime_tz_demo
    (id, event_name, dt_plain, dt_utc, dt_berlin, dt_ny)
VALUES
(
    2,
    'Morning sync',
    '2024-01-15 12:00:00',
    '2024-01-15 12:00:00',
    '2024-01-15 13:00:00',
    '2024-01-15 07:00:00'
);

During INSERT, ClickHouse does not store those strings as text. It first turns each string into a DateTime value. Because these are direct strings, ClickHouse uses the target column type to decide which timezone to apply. The dt_plain column doesn’t have a timezone, so ClickHouse uses the default timezone, which we just reset to UTC. The other columns use the timezone in their datatypes. 

The four strings we inserted look different, but after parsing they all point to the same instant: 2024-01-15 12:00:00 UTC. We can confirm that by converting the inserted values to Unix timestamps:

SELECT
    toUnixTimestamp(dt_plain),
    toUnixTimestamp(dt_utc),
    toUnixTimestamp(dt_berlin),
    toUnixTimestamp(dt_ny)
FROM datetime_tz_demo
WHERE id = 2;

   ┌─toUnixTimestamp(dt_plain)─┬─toUnixTimestamp(dt_utc)─┬─toUnixTimestamp(dt_berlin)─┬─toUnixTimestamp(dt_ny)─┐
1. │                1705320000 │              1705320000 │                 1705320000 │             1705320000 │ -- 1.71 billion
   └───────────────────────────┴─────────────────────────┴────────────────────────────┴────────────────────────┘

All four columns have the same Unix timestamp, so they store the same instant. When we read the row back, the values still print in their column timezones:

SELECT id, event_name, dt_plain, dt_utc, dt_berlin, dt_ny
FROM datetime_tz_demo
WHERE id = 2;

   ┌─id─┬─event_name───┬────────────dt_plain─┬──────────────dt_utc─┬───────────dt_berlin─┬───────────────dt_ny─┐
1. │  2 │ Morning sync │ 2024-01-15 12:00:00 │ 2024-01-15 12:00:00 │ 2024-01-15 13:00:00 │ 2024-01-15 07:00:00 │
   └────┴──────────────┴─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┘

So the important part is what happened before storage: each direct string was interpreted through the target column type.

session_timezone changes how plain DateTime strings are interpreted

In the previous insert, a plain DateTime string was read in UTC, because the server timezone was UTC. Now let’s change only one thing: set the session timezone to Tokyo before inserting the row.

SET session_timezone = 'Asia/Tokyo';

This matters only for dt_plain. The column is plain DateTime, so it does not have a timezone written in the type. When ClickHouse receives a direct string for this column, it has to get the timezone from somewhere else. With session_timezone set, it uses the session timezone. Now insert a direct string into the dt_plain column:

INSERT INTO datetime_tz_demo
    (id, event_name, dt_plain)
VALUES
(
    3,
    'Plain direct Tokyo',
    '2024-01-15 12:00:00'
);

The string still says 12:00, but this time it is 12:00 in Tokyo. To make the stored instant easier to see, switch the session timezone back to UTC before reading the row:

SET session_timezone = 'UTC';

SELECT id, event_name, dt_plain
FROM datetime_tz_demo
WHERE id = 3;

   ┌─id─┬─event_name─────────┬────────────dt_plain─┐
1. │  3 │ Plain direct Tokyo │ 2024-01-15 03:00:00 │
   └────┴────────────────────┴─────────────────────┘

The inserted string was '2024-01-15 12:00:00', but ClickHouse read it as Tokyo time because dt_plain has no timezone of its own and the session timezone was Asia/Tokyo. So the stored instant is:

2024-01-15 12:00:00 Asia/Tokyo = 2024-01-15 03:00:00 UTC

This is the same rule we saw in WHERE: ClickHouse used the default timezone for a plain DateTime value.

NOTE: If an INSERT statement doesn’t provide values for a DateTime column, its default value is 0. The statement above doesn’t provide values for dt_utc, dt_berlin, and dt_ny, so their values display as the beginning of the Unix epoch, adjusted for the timezone of each column:

┌─id─┬─event_name─────────┬────────────dt_plain─┬──────────────dt_utc─┬───────────dt_berlin─┬───────────────dt_ny─┐
│ 3  │ Plain direct Tokyo │ 2024-01-15 03:00:00 │ 1970-01-01 00:00:00 │ 1970-01-01 01:00:00 │ 1969-12-31 19:00:00 │
└────┴────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┘ 

Explicit-timezone columns use their own timezone

The previous example showed what happens with plain DateTime: because the column type does not contain a timezone, the session timezone can affect how a direct string is interpreted. Now let’s reset the session timezone to Tokyo, but insert direct strings into columns that already have timezones in their types.

SET session_timezone = 'Asia/Tokyo';

INSERT INTO datetime_tz_demo
    (id, event_name, dt_utc, dt_berlin, dt_ny)
VALUES
(
    4,
    'Explicit columns direct Tokyo',
    '2024-01-15 12:00:00',
    '2024-01-15 13:00:00',
    '2024-01-15 07:00:00'
);

Here the session timezone is Asia/Tokyo, but each target column already says which timezone should be used. The default timezone doesn’t change the way those values are displayed: 

SELECT id, event_name, dt_utc, dt_berlin, dt_ny
FROM datetime_tz_demo
WHERE id = 4;

   ┌─id─┬─event_name────────────────────┬──────────────dt_utc─┬───────────dt_berlin─┬───────────────dt_ny─┐
1. │  4 │ Explicit columns direct Tokyo │ 2024-01-15 12:00:00 │ 2024-01-15 13:00:00 │ 2024-01-15 07:00:00 │
   └────┴───────────────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┘

Even though the session timezone was Asia/Tokyo during the insert, these columns did not use Tokyo to interpret their direct strings.

toDateTime(...) is evaluated before insertion

There is one more important case. So far, we have looked at direct strings. For direct strings, the target column type decides how the string is interpreted: explicit-timezone columns use their own timezone, and plain DateTime uses the server timezone or the session timezone. toDateTime(...) works differently. It is evaluated before the value reaches the target column, so the target column does not parse the original string.

If toDateTime(...) has a timezone argument, ClickHouse uses that timezone:

toDateTime('2024-01-15 12:00:00', 'UTC')

If it has no timezone argument, ClickHouse uses the default.

Let’s set the session timezone to Tokyo again:

SET session_timezone = 'Asia/Tokyo';

Now insert a one-argument toDateTime(...) into the Berlin column:

INSERT INTO datetime_tz_demo
    (id, event_name, dt_berlin)
VALUES
(
    5,
    'toDateTime into Berlin',
    toDateTime('2024-01-15 12:00:00')
);

At first glance, it may look like the Berlin column should interpret the string as Berlin time. But it does not get the chance to do that. The toDateTime(...) expression has already turned the string into a DateTime value before insertion.

Now read the row back:

SELECT id, event_name, dt_berlin
FROM datetime_tz_demo
WHERE id = 5;

   ┌─id─┬─event_name─────────────┬───────────dt_berlin─┐
1. │  5 │ toDateTime into Berlin │ 2024-01-15 04:00:00 │
   └────┴────────────────────────┴─────────────────────┘

Why does the Berlin column show 04:00?

Because toDateTime('2024-01-15 12:00:00') was evaluated when Tokyo was the default timezone:

2024-01-15 12:00:00 Asia/Tokyo = 2024-01-15 03:00:00 UTC

That instant was then inserted into a DateTime('Europe/Berlin') column. On January 15, Berlin is one hour ahead of UTC, so 2024-01-15 03:00:00 UTC is displayed as 2024-01-15 04:00:00 in Berlin.

The target column did not parse the original string, the toDateTime(...) function did.

The rule for INSERTs

The examples above all come back to the same distinction: direct strings are handled by the target column, while toDateTime(...) is evaluated before the value reaches the target column.

For INSERT, keep these two cases separate. A direct date string is interpreted according to the target column type. If the target column has an explicit timezone, ClickHouse uses that timezone; otherwise it uses the default timezone. 

toDateTime(...) is different. It is evaluated before insertion, so the datatype of the target column isn’t considered. If toDateTime(...) has a timezone argument, ClickHouse uses it, otherwise it uses the default timezone. 

That is why the safest pattern is to pass the timezone explicitly:

INSERT INTO datetime_tz_demo
    (id, event_name, dt_plain, dt_utc, dt_berlin, dt_ny)
VALUES
(
    6,
    'Always explicit',
    toDateTime('2024-01-15 12:00:00', 'UTC'),
    toDateTime('2024-01-15 12:00:00', 'UTC'),
    toDateTime('2024-01-15 12:00:00', 'UTC'),
    toDateTime('2024-01-15 12:00:00', 'UTC')
);

SELECT dt_plain, dt_utc, dt_berlin, dt_ny
FROM datetime_tz_demo
WHERE id=6;

   ┌────────────dt_plain─┬──────────────dt_utc─┬───────────dt_berlin─┬───────────────dt_ny─┐
1. │ 2024-01-15 21:00:00 │ 2024-01-15 12:00:00 │ 2024-01-15 13:00:00 │ 2024-01-15 07:00:00 │
   └─────────────────────┴─────────────────────┴─────────────────────┴─────────────────────┘

Now ClickHouse does not have to guess which timezone the string belongs to. The inserted instant is defined by the query itself, not by the server timezone, the session timezone, or the target column type.

Daylight Saving Time Adds Gaps and Ambiguities

So far, every example has had a clean mapping between a local date string and a stored instant. When the timezone was known, ClickHouse could turn a string like '2024-01-15 12:00:00' into one exact point on the UTC timeline.

Daylight Saving Time makes this harder.

In timezones that use it, some local clock times do not exist, and some happen twice. This has nothing to do with session_timezone; it is a property of the timezone itself. Even if you always pass the timezone explicitly, ClickHouse still has to resolve the local clock time through the timezone rules.

Let’s take Europe/Berlin. On 2024-03-31, the clocks moved forward from 02:00 to 03:00. That means local times between 02:00 and 02:59:59 did not exist.

You can see the jump by comparing 01:30 and 03:30 in Berlin:

SELECT
    toDateTime('2024-03-31 01:30:00', 'Europe/Berlin') AS before_jump,
    toDateTime('2024-03-31 03:30:00', 'Europe/Berlin') AS after_jump,
    toUnixTimestamp(toDateTime('2024-03-31 03:30:00', 'Europe/Berlin'))
  - toUnixTimestamp(toDateTime('2024-03-31 01:30:00', 'Europe/Berlin')) AS seconds_between;

   ┌─────────before_jump─┬──────────after_jump─┬─seconds_between─┐
1. │ 2024-03-31 01:30:00 │ 2024-03-31 03:30:00 │            3600 │
   └─────────────────────┴─────────────────────┴─────────────────┘

On the clock, 01:30 and 03:30 look two hours apart. On the UTC timeline, they are only one hour, or 3600 seconds, apart. The missing hour was skipped by the DST transition.

The autumn transition has the opposite problem. On 2024-10-27, Berlin moved back from 03:00 to 02:00. That means the local time 02:30 happened twice: once before the clock moved back, and once after. Two different UTC instants can therefore print as the same Berlin clock time:

SELECT
    toDateTime('2024-10-27 00:30:00', 'UTC')::DateTime('Europe/Berlin') AS first_0230_local,
    toDateTime('2024-10-27 01:30:00', 'UTC')::DateTime('Europe/Berlin') AS second_0230_local,
    toUnixTimestamp(toDateTime('2024-10-27 01:30:00', 'UTC'))
  - toUnixTimestamp(toDateTime('2024-10-27 00:30:00', 'UTC')) AS seconds_between;

   ┌────first_0230_local─┬───second_0230_local─┬─seconds_between─┐
1. │ 2024-10-27 02:30:00 │ 2024-10-27 02:30:00 │            3600 │
   └─────────────────────┴─────────────────────┴─────────────────┘

Both values display as 2024-10-27 02:30:00 in Berlin, but they are different instants. They are one hour apart on the UTC timeline.

This is the same lesson as before, but with a sharper edge: a date string is not an instant by itself. It becomes an instant only after ClickHouse applies timezone rules. Around DST transitions, those rules can include missing hours and repeated hours.

That is why UTC is the safest storage timezone for event timestamps. UTC has no daylight saving time, so it has no missing local hour and no repeated local hour. Store the instant in UTC, and convert to local time only when you need to display it.

Which Timezone Does ClickHouse Use?

By now, the pattern should be familiar: the same date string can point to different instants depending on where it appears. When something looks confusing, ask one question first: which timezone does ClickHouse use to interpret this string? Use this table as a shortcut.

The most important distinction is between a direct string and toDateTime('...'). A direct string is interpreted through the column type. If the column type contains a timezone, ClickHouse uses that timezone. If the column is plain DateTime, ClickHouse uses the default timezone. toDateTime('...') is evaluated before it reaches the column, so it uses the default timezone. 

The only form with no hidden dependency is the one where the timezone is written explicitly:

toDateTime('2024-01-15 12:00:00', 'UTC')

If the string is meant to be interpreted in Berlin time, say that explicitly too:

toDateTime('2024-01-15 13:00:00', 'Europe/Berlin')

Conclusion

ClickHouse DateTime values do not store timezones. They store points in time. Timezones matter at the edges: when ClickHouse turns a string into a stored value, and when it formats that stored value back into text.

When something looks wrong, ask one question first: which timezone did ClickHouse use to interpret this string?

For production data, the safest habit is simple: store event timestamps in DateTime('UTC') or DateTime64(..., 'UTC'), and pass the timezone explicitly when parsing strings. 

Avoid relying on the server timezone, the session timezone, or the timezone of the machine running the query. That habit makes filters, inserts, tests, and distributed queries much easier to reason about.