---
url: 'https://altinity.com/blog/the-hidden-timezone-rules-of-datetime-in-clickhouse'
title: The Hidden Timezone Rules of DateTime in ClickHouse®
author:
  name: Alsu Giliazova
  url: 'https://altinity.com/author/alsu/'
date: '2026-06-29T10:12:09-07:00'
modified: '2026-07-09T14:57:07-07:00'
type: post
summary: 'Understand how ClickHouse parses and displays DateTime values, manages timezones, and handles DST to prevent unexpected query results and filtering errors.'
categories:
  - Blog
tags:
  - ClickHouse
  - datetime
  - daylight saving time
  - timezones
image: 'https://altinity.com/wp-content/uploads/2026/06/Datetime-Clickhouse.png'
published: true
---

# The Hidden Timezone Rules of DateTime in ClickHouse®

A `DateTime` value in [ClickHouse](https://altinity.com/clickhouse-database/) 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:

```
SELECTtimeZone() 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](https://docs.altinity.com/altinitycloud/userguide/cluster-explorer/query-tab/), 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_nyFROM datetime_tz_demoSETTINGS 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 `INSERT`s**

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.

| **Expression** | **DateTime** | **DateTime(‘UTC’)** | **DateTime(‘Europe/Berlin’)** |
| --- | --- | --- | --- |
| Direct string in `WHERE`, no `session_timezone` | Server timezone | UTC | Europe/Berlin |
| Direct string in `WHERE`, with `session_timezone` | Session timezone | UTC | Europe/Berlin |
| Direct string in `INSERT`, no `session_timezone` | Server timezone | UTC | Europe/Berlin |
| Direct string in `INSERT`, with `session_timezone` | Session timezone | UTC | Europe/Berlin |
| `toDateTime('...')`, no `session_timezone` | Server timezone | Server timezone | Server timezone |
| `toDateTime('...')`, with `session_timezone` | Session timezone | Session timezone | Session timezone |
| `toDateTime('...', 'TZ')` | Explicit TZ | Explicit TZ | Explicit TZ |

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.