How to convert timezones in ClickHouse

How do I convert UTC timestamps to local time zones in ClickHouse?

Use toTimeZone() and related functions to safely shift DateTime and DateTime64 values between time zones in ClickHouse.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why convert time zones in ClickHouse?

Reports built for global users require timestamps to appear in each user’s local zone.ClickHouse stores DateTime values in UTC by default, so explicit conversion prevents misleading dates and broken aggregations.

What functions perform timezone conversion?

toTimeZone(datetime, 'Zone') shifts a DateTime into the target IANA zone.
toDateTime(datetime, 'Zone') parses a string as DateTime in the given zone.
toDateTime64(datetime64, 'Zone') behaves the same for sub-second precision.

How do I convert order timestamps to the customer’s zone?

Join Orders with Customers, then wrap order_date in toTimeZone(order_date, Customers.preferred_tz).This returns each order in the shopper’s local time for receipts or analytics.

Example – US/Eastern

SELECT id, toTimeZone(order_date, 'US/Eastern') AS order_date_estFROM OrdersLIMIT 5;

Can I convert and format in one step?

Yes. Combine toTimeZone() with formatDateTime(): formatDateTime(toTimeZone(order_date,'Europe/Berlin'),'%Y-%m-%d %H:%M') outputs a ready-to-display string.

Best practices for timezone safety

• Store source timestamps in UTC.
• Keep zone identifiers in a dimension table (e.g., Customers.preferred_tz).
• Avoid hard-coding offsets; use IANA names so DST is handled automatically.

Common mistakes

Hard-coding offsets: ‘+02:00’ ignores DST.Fix by using ‘Europe/Berlin’.
Converting twice: Applying toTimeZone() on already shifted data returns wrong times. Track conversion state in the query.

When should I use toDateTime vs. toTimeZone?

toDateTime parses strings; toTimeZone shifts existing DateTime values. Use both when ingesting text then converting.

Does timezone conversion affect indexing?

No. Conversions happen at query time. Store UTC in primary keys for optimal index usage.

Why How to convert timezones in ClickHouse is important

How to convert timezones in ClickHouse Example Usage


SELECT o.id,
       c.name,
       toTimeZone(o.order_date,'America/Los_Angeles')  AS order_date_pst,
       toTimeZone(o.order_date,'Asia/Tokyo')           AS order_date_jst,
       o.total_amount
FROM Orders AS o
WHERE o.order_date >= toDateTime('2024-01-01 00:00:00')
ORDER BY o.order_date
LIMIT 10;

How to convert timezones in ClickHouse Syntax


toTimeZone(datetime\_expr, 'IANA/Zone')
-- datetime_expr: DateTime or DateTime64 column or expression
-- 'IANA/Zone': case-sensitive time zone, e.g., 'Europe/Berlin'

-- Convert Orders.order_date to customer zone stored in Customers.time_zone
SELECT o.id,
       toTimeZone(o.order_date, c.time_zone) AS local_order_date
FROM Orders AS o
JOIN Customers AS c ON c.id = o.customer_id;

Common Mistakes

Hard-coding numeric offsets like +02:00. These ignore daylight-saving shifts. Use IANA zone names such as 'Europe/Berlin' so ClickHouse applies DST automatically.
Applying toTimeZone() multiple times on the same column, leading to double-shifted results. Track whether data is already in local time or keep it UTC until final presentation.