---
title: "How to convert DateTimes to a different timezone in ClickHouse®"
excerpt: "Learn how to convert datetime values between timezones in ClickHouse® using toTimeZone function with practical examples, best practices, and common pitfalls to avoid."
authors: "Cameron Archer"
categories: "AI Resources"
createdOn: "2025-09-30 17:17:32"
publishedOn: "2025-09-30 17:17:32"
updatedOn: "2025-09-30 17:17:32"
status: "published"
---

Working with global applications means dealing with timestamps from different timezones, and ClickHouse{% sup %}®{% /sup %}'s `toTimeZone` function handles this conversion while preserving the exact moment in time. The function takes a DateTime value and shifts its display to match any target timezone without changing when the event actually occurred.

This guide walks through the complete syntax, common pitfalls, performance considerations, and practical examples for converting datetime values between timezones in ClickHouse{% sup %}®{% /sup %}. You'll learn how to handle daylight saving transitions, work around dynamic timezone limitations, and build timezone-aware analytics APIs.

## What toTimeZone does and when to use it

The `toTimeZone` function takes a DateTime or DateTime64 value and converts it from one timezone to another while keeping the same moment in time. Think of it like changing the clock display without changing when something actually happened.

When you call `toTimeZone(datetime_value, 'Target/TimeZone')`, ClickHouse{% sup %}®{% /sup %} shifts the time representation to match the target timezone. If you have a UTC timestamp showing 10:00 AM, converting it to US Eastern time during winter gives you 5:00 AM - same moment, different display.

You'll find yourself reaching for timezone conversion in a few common scenarios:

- **User localization:** Show dashboard times in each user's local timezone instead of UTC
- **Business reporting:** Convert server logs to company headquarters timezone for daily reports
- **Multi-region analytics:** Display regional performance data in local business hours

## Basic syntax and parameters

The function follows a simple pattern: `toTimeZone(your_datetime_column, 'Timezone/Name')`. The first part is your datetime data, and the second tells ClickHouse{% sup %}®{% /sup %} where to convert it.

```sql
SELECT
    event_time,
    toTimeZone(event_time, 'Europe/Berlin') AS berlin_time
FROM events;
```

### Parameter requirements

Your datetime input works with DateTime or DateTime64 columns, but strings won't work directly. If you have string timestamps, you'll need to convert them first with `toDateTime()`.

The timezone parameter only accepts string literals - you can't use column values or variables here. ClickHouse{% sup %}®{% /sup %} needs to know the timezone at query planning time, not during execution.

### Return types

Whatever type you put in comes back out. DateTime input gives you DateTime output, DateTime64 keeps its precision level. The difference is that the returned value carries new timezone information that changes how ClickHouse{% sup %}®{% /sup %} displays the timestamp.

```sql
-- Input: 2024-01-15 10:00:00 (UTC)
-- Output: 2024-01-15 11:00:00 (Europe/Berlin)
```

### Constant vs non-constant zone arguments

Here's where things get tricky. ClickHouse{% sup %}®{% /sup %} requires the timezone to be a constant string, which means this works:

```sql
SELECT toTimeZone(created_at, 'US/Pacific') FROM orders;
```

But this fails:

```sql
SELECT toTimeZone(created_at, user_timezone) FROM orders;
-- Error: Timezone must be const string
```

The limitation exists because ClickHouse{% sup %}®{% /sup %} needs to resolve timezone conversions when it plans the query, not when it runs.

## Supported time zone names in ClickHouse{% sup %}®{% /sup %}

ClickHouse{% sup %}®{% /sup %} uses the IANA timezone database - the same standard that powers Linux systems and most programming languages. These names follow the 'Continent/City' pattern like 'America/New_York' or 'Europe/London'.

### Querying system.time_zones

You can explore available timezones directly in ClickHouse{% sup %}®{% /sup %}:

```sql
SELECT name FROM system.time_zones
WHERE name LIKE '%New_York%' OR name LIKE '%UTC%'
ORDER BY name;
```

This query shows you the exact strings ClickHouse{% sup %}®{% /sup %} recognizes, which helps avoid typos that cause runtime errors.

### Common aliases and abbreviations

While full IANA names work best, ClickHouse{% sup %}®{% /sup %} supports some common shortcuts:

- **UTC variants:** 'UTC', 'GMT', 'Zulu'
- **US regions:** 'US/Eastern', 'US/Central', 'US/Mountain', 'US/Pacific'
- **European zones:** 'CET', 'EET', 'WET'

However, using complete names like 'America/New_York' instead of 'US/Eastern' gives you more explicit control over daylight saving behavior.

## Convert UTC timestamps to local zones step by step

Let's walk through converting UTC order data to US Eastern time with a real example.

### 1. Select raw UTC data

Start by looking at your source data:

```sql
SELECT
    order_id,
    created_at,
    total_amount
FROM orders
WHERE created_at >= '2024-01-01'
LIMIT 5;
```

This shows your original UTC timestamps before any conversion.

### 2. Apply toTimeZone with literal zone

Add the timezone conversion:

```sql
SELECT
    order_id,
    created_at AS utc_time,
    toTimeZone(created_at, 'US/Eastern') AS eastern_time,
    total_amount
FROM orders
WHERE created_at >= '2024-01-01'
LIMIT 5;
```

The hour values shift by 4-5 hours depending on whether daylight saving time is active.

### 3. Validate daylight saving shifts

Test across daylight saving boundaries:

```sql
SELECT
    created_at AS utc_time,
    toTimeZone(created_at, 'US/Eastern') AS eastern_time,
    formatDateTime(toTimeZone(created_at, 'US/Eastern'), '%Y-%m-%d %H:%M:%S %z') AS with_offset
FROM orders
WHERE created_at BETWEEN '2024-03-09' AND '2024-03-11'
ORDER BY created_at;
```

You'll see the offset change from -05:00 to -04:00 as daylight saving begins, confirming automatic DST handling.

## Convert and format in one query

Often you want both timezone conversion and formatting for display purposes.

### formatDateTime with zone placeholder

The `formatDateTime` function includes timezone placeholders:

```sql
SELECT
    order_id,
    formatDateTime(toTimeZone(created_at, 'Europe/Berlin'), '%Y-%m-%d %H:%M:%S %z') AS formatted_berlin
FROM orders;
```

The `%z` placeholder shows the timezone offset (+0100), while `%Z` displays the abbreviation (CET).

### Combining toTimeZone and formatDateTime

Nest the functions for single-step conversion and formatting:

```sql
SELECT
    order_id,
    formatDateTime(
        toTimeZone(created_at, 'Asia/Tokyo'),
        '%b %d, %Y at %I:%M %p JST'
    ) AS tokyo_display
FROM orders
WHERE created_at >= today() - INTERVAL 1 DAY;
```

This produces readable output like "Jan 15, 2024 at 07:30 PM JST" for user interfaces.

## Convert using dynamic zone values

While `toTimeZone` requires constant timezone strings, you can work around this limitation.

### Using zone stored in another column

Direct column usage fails:

```sql
-- This produces an error
SELECT toTimeZone(event_time, user_timezone) FROM user_events;
-- Error: Timezone must be const string
```

### Work around with dictGet or CASE

For limited timezone sets, use CASE statements:

```sql
SELECT
    event_time,
    CASE user_region
        WHEN 'US_EAST' THEN toTimeZone(event_time, 'US/Eastern')
        WHEN 'US_WEST' THEN toTimeZone(event_time, 'US/Pacific')
        WHEN 'EUROPE' THEN toTimeZone(event_time, 'Europe/London')
        ELSE event_time
    END AS localized_time
FROM user_events;
```

For larger timezone mappings, consider creating separate queries for each timezone group or using dictionaries with multiple query approaches.

## toTimeZone vs toDateTime vs toDateTime64

Each function serves different conversion needs:

| Function | Input Type | Primary Use | Timezone Handling |
| -------- | ---------- | ----------- | ------------------ |
| toTimeZone | DateTime/DateTime64 | Convert existing timestamps | Changes timezone metadata |
| toDateTime | String/Number | Parse new timestamps | Sets initial timezone |
| toDateTime64 | String/Number | Parse with sub-second precision | Sets timezone with microseconds |

### When to prefer toDateTime

Use `toDateTime` when parsing string representations with timezone information:

```sql
-- Parsing strings with timezone info
SELECT toDateTime('2024-01-15 10:30:00', 'Europe/Berlin') AS parsed_time;

-- Converting existing DateTime values
SELECT toTimeZone(existing_datetime, 'Europe/Berlin') AS converted_time;
```

The key difference: `toDateTime` creates new DateTime values from strings, while `toTimeZone` converts existing ones.

### Why use DateTime64 for sub-second precision

When working with high-frequency data, `toTimeZone` preserves precision levels:

```sql
SELECT
    high_precision_timestamp,
    toTimeZone(high_precision_timestamp, 'US/Pacific') AS pacific_precise
FROM trading_events
WHERE timestamp >= now() - INTERVAL 1 HOUR;
```

The conversion maintains all decimal places in your timestamp precision.

## Performance and index impact of timezone conversion

Timezone conversion affects query performance, especially in sorting and filtering operations.

### Effect on sorting keys

Using `toTimeZone` in ORDER BY prevents ClickHouse{% sup %}®{% /sup %} from using existing datetime indexes:

```sql
-- Slower: conversion during sorting
SELECT * FROM events
ORDER BY toTimeZone(event_time, 'US/Pacific') DESC;

-- Faster: sort on original column
SELECT
    event_id,
    toTimeZone(event_time, 'US/Pacific') AS pacific_time
FROM events
ORDER BY event_time DESC;
```

The second approach leverages existing indexes while providing converted timestamps.

### Materialized view strategies

Pre-convert timezones in [materialized views](https://tinybird.co/docs/forward/work-with-data/optimize/materialized-views) for frequently queried zones:

```tinybird
NODE localized_events
SQL >
    SELECT
        event_id,
        event_time AS utc_time,
        toTimeZone(event_time, 'US/Eastern') AS eastern_time,
        toTimeZone(event_time, 'Europe/London') AS london_time,
        user_id,
        event_type
    FROM raw_events

TYPE MATERIALIZED
DATASOURCE localized_events_mv
```

This eliminates runtime conversion costs for common timezone combinations.

### Determinism and caching

The `toTimeZone` function produces identical outputs for identical inputs, allowing ClickHouse{% sup %}®{% /sup %} to cache conversion results within query execution. However, DST transitions create edge cases where the same local time can represent two different UTC moments during "fall back" periods.

## Common mistakes to avoid

Several patterns lead to incorrect results or performance issues:

- **Invalid timezone strings:** Using 'PST' instead of 'US/Pacific'
- **Wrong input types:** Passing strings without DateTime casting
- **Double conversion:** Applying timezone conversion multiple times

### Passing invalid zone strings

ClickHouse{% sup %}®{% /sup %} rejects unrecognized timezone names immediately:

```sql
-- This fails
SELECT toTimeZone(event_time, 'PST') FROM events;
-- Error: Unknown time zone: PST

-- Use this instead
SELECT toTimeZone(event_time, 'US/Pacific') FROM events;
```

Always verify timezone names against the `system.time_zones` table before production use.

### Forgetting to cast strings to DateTime

String datetime values need explicit conversion:

```sql
-- This produces an error
SELECT toTimeZone('2024-01-15 10:00:00', 'Europe/Berlin');

-- Cast strings first
SELECT toTimeZone(toDateTime('2024-01-15 10:00:00'), 'Europe/Berlin');
```

### Applying conversion twice

Converting already-converted timestamps produces incorrect results:

```sql
-- Wrong: double conversion
SELECT toTimeZone(toTimeZone(utc_time, 'US/Eastern'), 'US/Eastern') FROM events;

-- Correct: single conversion
SELECT toTimeZone(utc_time, 'US/Eastern') FROM events;
```

Track which columns contain converted values to avoid this multiplication error.

## Best practices for timezone safety

Following these guidelines prevents timezone-related bugs in production systems.

### Store timestamps in UTC

Keep all stored timestamps in UTC, converting only during read operations:

```sql
-- Good: UTC storage schema
CREATE TABLE events (
    event_id UInt64,
    event_time DateTime('UTC'),
    user_id UInt64
) ENGINE = MergeTree()
ORDER BY (event_time, user_id);
```

This approach eliminates ambiguity about stored data timezone and simplifies data pipeline logic.

### Convert at read time only

Apply timezone conversion in SELECT queries rather than during data ingestion:

```sql
-- Convert for display, keep storage in UTC
SELECT
    event_id,
    toTimeZone(event_time, 'US/Pacific') AS display_time,
    user_id
FROM events
WHERE event_time >= today() - INTERVAL 7 DAY;
```

This pattern maintains data consistency while providing flexible timezone display options.

### Document your zone assumptions

Clearly document timezone expectations in schema definitions, API documentation, and data pipeline code. Include comments specifying whether datetime columns contain UTC or local times, and note any conversion requirements for downstream consumers.

## Building an example time-series analytics API with Tinybird

Time series data is everywhere. Crypto, for example. Crypto transactions happen 24/7 across all timezones. Here's how to build a complete timezone-aware crypto analytics API using [Tinybird's](https://www.tinybird.co) managed ClickHouse{% sup %}®{% /sup %} platform that converts UTC transaction data to users' local timezones.

### Step 1: Create a data source for crypto transactions

First, create a data source to store global crypto transactions. Create a `crypto_transactions.datasource` file:

```tinybird
SCHEMA >
    `transaction_id` String,
    `timestamp_utc` DateTime64(3, 'UTC'),
    `user_id` String,
    `wallet_address` String,
    `transaction_type` LowCardinality(String),
    `cryptocurrency` LowCardinality(String),
    `amount_crypto` Float64,
    `amount_usd` Float64,
    `fees_usd` Float64,
    `exchange` LowCardinality(String),
    `user_timezone` String

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(timestamp_utc)"
ENGINE_SORTING_KEY "timestamp_utc, user_id"
```

Deploy the data source:

```bash
tb --cloud deploy
```

Ingest sample crypto transaction data using the [Events API](https://www.tinybird.co/docs/forward/get-data-in/events-api):

```bash
curl -X POST \
  -H "Authorization: Bearer $TINYBIRD_TOKEN" \
  -H "Content-Type: application/json" \
  "https://api.tinybird.co/v0/events?name=crypto_transactions" \
  -d '[
    {"transaction_id": "tx_001", "timestamp_utc": "2024-12-01 14:30:00", "user_id": "user_001", "wallet_address": "0x123...abc", "transaction_type": "buy", "cryptocurrency": "BTC", "amount_crypto": 0.025, "amount_usd": 2450.00, "fees_usd": 12.25, "exchange": "coinbase", "user_timezone": "America/New_York"},
    {"transaction_id": "tx_002", "timestamp_utc": "2024-12-01 22:15:00", "user_id": "user_002", "wallet_address": "0x456...def", "transaction_type": "sell", "cryptocurrency": "ETH", "amount_crypto": 1.5, "amount_usd": 3750.00, "fees_usd": 18.75, "exchange": "binance", "user_timezone": "Europe/London"},
    {"transaction_id": "tx_003", "timestamp_utc": "2024-12-02 03:45:00", "user_id": "user_003", "wallet_address": "0x789...ghi", "transaction_type": "swap", "cryptocurrency": "SOL", "amount_crypto": 50.0, "amount_usd": 12500.00, "fees_usd": 25.00, "exchange": "kraken", "user_timezone": "Asia/Tokyo"}
  ]'
```

### Step 2: Create a timezone-aware transaction analytics API

Create a pipe that converts UTC timestamps to users' local timezones for portfolio analysis. Create `user_transaction_history.pipe`:

```tinybird
TOKEN "crypto_analytics_read" READ

NODE transaction_analysis
SQL >
    %
    SELECT
        transaction_id,
        user_id,
        -- Convert UTC to user's local timezone
        toTimeZone(timestamp_utc, user_timezone) AS local_timestamp,
        toDate(toTimeZone(timestamp_utc, user_timezone)) AS local_date,
        toHour(toTimeZone(timestamp_utc, user_timezone)) AS local_hour,
        -- Original UTC timestamp for reference
        timestamp_utc,
        user_timezone,
        transaction_type,
        cryptocurrency,
        amount_crypto,
        amount_usd,
        fees_usd,
        exchange,
        -- Calculate daily trading patterns in user's timezone
        CASE
            WHEN toHour(toTimeZone(timestamp_utc, user_timezone)) BETWEEN 9 AND 17
            THEN 'market_hours'
            WHEN toHour(toTimeZone(timestamp_utc, user_timezone)) BETWEEN 18 AND 23
            THEN 'evening'
            ELSE 'night_early_morning'
        END AS trading_period,
        -- Running total in user's local day
        sum(amount_usd) OVER (
            PARTITION BY user_id, toDate(toTimeZone(timestamp_utc, user_timezone))
            ORDER BY toTimeZone(timestamp_utc, user_timezone)
        ) AS daily_volume_running_total
    FROM crypto_transactions
    WHERE timestamp_utc >= {{DateTime(start_time, '2024-12-01 00:00:00')}}
      AND timestamp_utc < {{DateTime(end_time, '2024-12-03 00:00:00')}}
    {\% if defined(user_id_filter) %}
      AND user_id = {{String(user_id_filter)}}
    {\% end %}
    {\% if defined(timezone_filter) %}
      AND user_timezone = {{String(timezone_filter)}}
    {\% end %}
    {\% if defined(crypto_filter) %}
      AND cryptocurrency = {{String(crypto_filter)}}
    {\% end %}
    {\% if defined(transaction_type_filter) %}
      AND transaction_type = {{String(transaction_type_filter)}}
    {\% end %}
    ORDER BY toTimeZone(timestamp_utc, user_timezone) DESC
    {\% if defined(limit) %}
    LIMIT {{Int32(limit, 100)}}
    {\% end %}

TYPE ENDPOINT
```

Deploy the API endpoint:

```bash
tb --cloud deploy
```

### Step 3: Create timezone-aware portfolio summary API

Create another pipe for aggregated portfolio insights by timezone. Create `portfolio_summary_by_timezone.pipe`:

```tinybird
TOKEN "portfolio_read" READ

NODE portfolio_summary
SQL >
    %
    SELECT
        user_timezone,
        toDate(toTimeZone(timestamp_utc, user_timezone)) AS trading_date,
        transaction_type,
        cryptocurrency,
        count() AS transaction_count,
        sum(amount_crypto) AS total_crypto_amount,
        sum(amount_usd) AS total_usd_volume,
        sum(fees_usd) AS total_fees,
        avg(amount_usd) AS avg_transaction_size,
        min(toTimeZone(timestamp_utc, user_timezone)) AS first_transaction_local,
        max(toTimeZone(timestamp_utc, user_timezone)) AS last_transaction_local,
        -- Most active trading hour in local timezone
        topK(1)(toHour(toTimeZone(timestamp_utc, user_timezone)))[1] AS most_active_hour_local
    FROM crypto_transactions
    WHERE timestamp_utc >= {{DateTime(start_date, '2024-12-01 00:00:00')}}
      AND timestamp_utc < {{DateTime(end_date, '2024-12-03 00:00:00')}}
    {\% if defined(timezone_filter) %}
      AND user_timezone = {{String(timezone_filter)}}
    {\% end %}
    {\% if defined(crypto_filter) %}
      AND cryptocurrency = {{String(crypto_filter)}}
    {\% end %}
    GROUP BY user_timezone, trading_date, transaction_type, cryptocurrency
    ORDER BY total_usd_volume DESC
    {\% if defined(limit) %}
    LIMIT {{Int32(limit, 50)}}
    {\% end %}

TYPE ENDPOINT
```

Deploy the portfolio API:

```bash
tb --cloud deploy
```

### Step 4: Query the timezone-aware crypto APIs

Your crypto analytics APIs are now available with timezone conversion. Query them for different analysis scenarios:

```bash
# Get transaction history for a specific user in their local timezone
curl "https://api.tinybird.co/v0/pipes/user_transaction_history.json?start_time=2024-12-01%2000:00:00&end_time=2024-12-02%2023:59:59&user_id_filter=user_001" \
  -H "Authorization: Bearer $TINYBIRD_TOKEN"

# Analyze Bitcoin transactions across all timezones
curl "https://api.tinybird.co/v0/pipes/user_transaction_history.json?start_time=2024-12-01%2000:00:00&end_time=2024-12-02%2023:59:59&crypto_filter=BTC" \
  -H "Authorization: Bearer $TINYBIRD_TOKEN"

# Get portfolio summary for European users
curl "https://api.tinybird.co/v0/pipes/portfolio_summary_by_timezone.json?start_date=2024-12-01%2000:00:00&end_date=2024-12-02%2023:59:59&timezone_filter=Europe/London" \
  -H "Authorization: Bearer $TINYBIRD_TOKEN"
```

The APIs return timezone-converted data showing local timestamps alongside portfolio insights:

```json
{
    "meta": [
        {"name": "transaction_id", "type": "String"},
        {"name": "user_id", "type": "String"},
        {"name": "local_timestamp", "type": "DateTime"},
        {"name": "local_date", "type": "Date"},
        {"name": "local_hour", "type": "UInt8"},
        {"name": "timestamp_utc", "type": "DateTime64"},
        {"name": "user_timezone", "type": "String"},
        {"name": "transaction_type", "type": "String"},
        {"name": "cryptocurrency", "type": "String"},
        {"name": "amount_crypto", "type": "Float64"},
        {"name": "amount_usd", "type": "Float64"},
        {"name": "trading_period", "type": "String"},
        {"name": "daily_volume_running_total", "type": "Float64"}
    ],
    "data": [
        {
            "transaction_id": "tx_001",
            "user_id": "user_001",
            "local_timestamp": "2024-12-01 09:30:00",
            "local_date": "2024-12-01",
            "local_hour": 9,
            "timestamp_utc": "2024-12-01 14:30:00",
            "user_timezone": "America/New_York",
            "transaction_type": "buy",
            "cryptocurrency": "BTC",
            "amount_crypto": 0.025,
            "amount_usd": 2450.0,
            "trading_period": "market_hours",
            "daily_volume_running_total": 2450.0
        }
    ],
    "rows": 1,
    "statistics": {
        "elapsed": 0.004,
        "rows_read": 3,
        "bytes_read": 384
    }
}
```

This approach provides:

- **Timezone-aware transaction history** with local timestamps for better user experience
- **Trading pattern analysis** showing when users are most active in their local time
- **Portfolio insights** aggregated by local trading days rather than UTC days
- **Flexible filtering** by user, timezone, cryptocurrency, and transaction type
- **Running totals** calculated correctly within each user's local trading day

Your time series crypt transaction data becomes available as production-ready APIs that automatically handle timezone conversion for global users. [Start building with Tinybird's free plan](https://cloud.tinybird.co/signup) to create timezone-aware analytics APIs.

## Additional resources

- [Date and Time Functions](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions)
- [Type Conversion Functions](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions)
- [DateTime Data Types](https://clickhouse.com/docs/en/sql-reference/data-types/datetime)
- [Database Timestamps and Timezones Best Practices](https://www.tinybird.co/blog-posts/database-timestamps-timezones)
- [Tinybird vs. ClickHouse{% sup %}®{% /sup %}: What's the difference?](https://www.tinybird.co/blog-posts/tinybird-vs-clickhouse)

## Frequently asked questions about ClickHouse{% sup %}®{% /sup %} timezone conversion

### Is toTimeZone deterministic across ClickHouse{% sup %}®{% /sup %} versions?

Yes, `toTimeZone` produces consistent results across ClickHouse{% sup %}®{% /sup %} versions since it uses the standard IANA timezone database for calculations. The underlying timezone data gets updated periodically, but conversion logic remains stable between releases.

### How do I list daylight saving transitions for a zone?

Query specific dates around March and November to observe DST behavior:

```sql
SELECT
    d,
    toTimeZone(toDateTime(d || ' 12:00:00'), 'US/Eastern') as eastern_time,
    formatDateTime(toTimeZone(toDateTime(d || ' 12:00:00'), 'US/Eastern'), '%z') as offset
FROM (
    SELECT arrayJoin(['2024-03-10', '2024-03-11', '2024-11-03', '2024-11-04']) as d
);
```

### Can I index a column produced by toTimeZone?

No, you cannot directly index function call results, but you can create materialized views with pre-converted timezone values and index those columns. This approach provides performance benefits while maintaining timezone conversion functionality.