How to retrieve current session timezone using timeZone() in ClickHouse^®

Timezones can affect how data and timestamps are displayed or interpreted in databases. In ClickHouse®, each session has a timezone setting that controls this behavior. Sometimes, it is helpful to know exactly which timezone is active for the current session.

The session timezone determines how ClickHouse® interprets and displays date and time values for your queries. This value can be set by the server, by your client, or be explicitly configured per session.

Knowing how to check the current session timezone allows for better understanding of how ClickHouse® handles time-related functions and data.

Retrieve the session timezone with timezone()

The timezone() function returns the name of the timezone for your current session. This is the most direct way to check which timezone ClickHouse® is using for your queries.

SELECT timezone();

The function returns a string with the IANA timezone name, like UTC, Europe/Warsaw, or America/New_York. This is the timezone that ClickHouse® uses when it converts timestamps to readable dates and times.

Here's what the output looks like for different timezones:

• UTC timezone: Returns UTC for sessions using Coordinated Universal Time
• European timezone: Returns Europe/London for London time
• American timezone: Returns America/New_York for Eastern time

The function always returns a string type, so you can use it in WHERE clauses or combine it with other string functions if needed.

Why ClickHouse® stores all timestamps as UTC internally

ClickHouse® stores every DateTime and DateTime64 value as a UTC timestamp. This means all your date and time data gets saved as the number of seconds since January 1, 1970, 00:00:00 UTC.

The timezone information isn't stored with each timestamp. Instead, ClickHouse® keeps timezone info in the column definition or uses your session timezone setting. This approach keeps storage efficient and lets you view the same data in different timezones.

When you see a timestamp like 2024-09-18 14:30:00, ClickHouse® is showing you the UTC value converted to your session timezone. The actual stored value is still the UTC timestamp - only the display changes.

Session timezone versus server timezone

Your session timezone can be different from the server timezone. ClickHouse® follows a specific order when deciding which timezone to use:

• Client timezone setting: If your client specifies a timezone, ClickHouse® uses that
• Server timezone: If no client timezone is set, ClickHouse® uses the server's timezone
• UTC default: If neither client nor server specifies a timezone, ClickHouse® defaults to UTC

The clickhouse-client normally uses the server timezone. To use your local machine's timezone instead, add the --use_client_time_zone flag when connecting.

Change your session timezone

You can set your session timezone in several ways depending on how you connect to ClickHouse®.

Using clickhouse-client

Connect with a specific timezone using the --timezone flag:

clickhouse-client --timezone=America/New_York

Or use your local timezone:

clickhouse-client --use_client_time_zone

You can also change the timezone temporarily within a session:

SET time_zone = 'Asia/Tokyo';
SELECT now();

Using HTTP interface

Add the timezone as a query parameter:

curl 'http://localhost:8123/?timezone=Europe/London' -d 'SELECT now();'

Or include it as an HTTP header:

curl -H "X-ClickHouse®-Timezone: Europe/London" -d "SELECT now()" 'http://localhost:8123/'

Setting permanent user timezone

Configure a default timezone for a user in the server's users.xml file:

<user>
    <name>analyst</name>
    <profile>
        <timezone>Asia/Singapore</timezone>
    </profile>
</user>

Handle timezone conversion with DateTime columns

ClickHouse® lets you specify timezones at the column level, which overrides session settings for that specific column.

Column-level timezone specification

When creating a table, you can set a timezone for specific DateTime columns:

CREATE TABLE events (
  ts_utc DateTime('UTC'),
  ts_ny  DateTime('America/New_York'),
  value  UInt32
) ENGINE = MergeTree ORDER BY ts_utc;

A column defined as DateTime('Asia/Tokyo') always shows values in Tokyo time, regardless of your session timezone.

Converting between timezones

The toDateTime() function accepts a timezone parameter:

SELECT toDateTime('2024-09-18 10:00:00', 'Europe/London');

The toTimeZone() function changes how an existing DateTime value is displayed:

SELECT toTimeZone(now(), 'Asia/Tokyo');

This doesn't change the underlying timestamp - it just shows the same moment in a different timezone.

List all supported timezones

ClickHouse® maintains a system table with all supported timezones. Use this to find valid timezone names:

SELECT * FROM system.time_zones;

To search for specific regions:

-- Find all European timezones
SELECT * FROM system.time_zones WHERE time_zone LIKE 'Europe/%';

-- Find timezones containing "Tokyo"
SELECT * FROM system.time_zones WHERE time_zone LIKE '%Tokyo%';

The list includes hundreds of IANA timezone names and gets updated with each ClickHouse® release.

Debug timezone issues in three steps

When timestamps don't match what you expect, follow these steps to identify the problem.

1. Compare session time with UTC

Run this query to see both your session time and UTC time:

SELECT
  now() AS session_now,
  now() AT TIME ZONE 'UTC' AS utc_now;

If you're in Europe/Warsaw during summer time, you might see:

┌─────────session_now─┬──────────utc_now─┐
│ 2024-09-18 14:45:00 │ 2024-09-18 12:45:00 │
└─────────────────────┴─────────────────────┘

The two-hour difference is expected. If the difference seems wrong, check your session timezone setting.

2. Verify timezone name exists

Make sure your timezone name is valid by checking the system table:

SELECT * FROM system.time_zones WHERE time_zone = 'Europe/Warsaw';

If this returns no results, the timezone name isn't recognized. Use the full IANA format like America/New_York, not abbreviations like EST.

3. Check connection settings

Review how you're connecting to ClickHouse®:

• Command line flags: Check for --timezone or --use_client_time_zone flags
• HTTP parameters: Look for timezone parameters in your requests
• User configuration: Verify user profile settings in the server config

These settings can override the default timezone and cause unexpected behavior.

Build a timezone-aware analytics API

Let's build a real-world example using Tinybird, a managed ClickHouse® service that makes it easy to create and host high-performance ClickHouse®-backed APIs. We'll create a global e-commerce analytics API that properly handles timezone differences between the ClickHouse® server timezone and user data timezones. For scaling real-time analytics with billions of rows, proper timezone handling becomes critical for accurate cross-regional analysis.

Streaming global transaction data

Start with high-volume e-commerce transaction data from customers worldwide. Create an NDJSON data source for real-time streaming:

{"transaction_id": "tx_001", "user_id": "u_1001", "timestamp_utc": "2025-09-18 14:32:45", "amount": 129.99, "currency": "USD", "country": "US", "user_timezone": "America/New_York", "product_category": "electronics", "payment_method": "credit_card"}
{"transaction_id": "tx_002", "user_id": "u_1002", "timestamp_utc": "2025-09-18 14:33:12", "amount": 89.50, "currency": "EUR", "country": "DE", "user_timezone": "Europe/Berlin", "product_category": "clothing", "payment_method": "paypal"}
{"transaction_id": "tx_003", "user_id": "u_1003", "timestamp_utc": "2025-09-18 14:33:45", "amount": 15999.00, "currency": "JPY", "country": "JP", "user_timezone": "Asia/Tokyo", "product_category": "electronics", "payment_method": "credit_card"}
{"transaction_id": "tx_004", "user_id": "u_1004", "timestamp_utc": "2025-09-18 14:34:18", "amount": 45.75, "currency": "GBP", "country": "GB", "user_timezone": "Europe/London", "product_category": "books", "payment_method": "debit_card"}

Set up real-time streaming ingestion using the Tinybird Events API to handle thousands of transactions per second globally:

# Stream transaction events directly to Tinybird via HTTP POST
curl -X POST \
  -H "Authorization: Bearer YOUR_TINYBIRD_TOKEN" \
  -H "Content-Type: application/json" \
  "https://api.tinybird.co/v0/events?name=global_transactions" \
  -d '[
    {"transaction_id": "tx_001", "user_id": "u_1001", "timestamp_utc": "2025-09-18 14:32:45", "amount": 129.99, "currency": "USD", "country": "US", "user_timezone": "America/New_York", "product_category": "electronics", "payment_method": "credit_card"},
    {"transaction_id": "tx_002", "user_id": "u_1002", "timestamp_utc": "2025-09-18 14:33:12", "amount": 89.50, "currency": "EUR", "country": "DE", "user_timezone": "Europe/Berlin", "product_category": "clothing", "payment_method": "paypal"},
    {"transaction_id": "tx_003", "user_id": "u_1003", "timestamp_utc": "2025-09-18 14:33:45", "amount": 15999.00, "currency": "JPY", "country": "JP", "user_timezone": "Asia/Tokyo", "product_category": "electronics", "payment_method": "credit_card"}
  ]'

The Events API automatically creates the global_transactions data source with schema inference from the JSON payload, enabling immediate real-time ingestion without pre-defining schemas or file uploads.

Building timezone-aware revenue analytics

Create a pipe that compares the ClickHouse® server timezone against user data timezones for accurate regional analysis:

NODE timezone_revenue_analytics
SQL >
    %
    SELECT
        country,
        user_timezone,
        -- Get ClickHouse® server timezone for comparison and debugging
        timezone() AS server_timezone,
        -- Convert server time (UTC) to user's local timezone
        toTimeZone(parseDateTime64BestEffort(timestamp_utc), user_timezone) AS local_transaction_time,
        -- Verify timezone offset difference for data validation
        round(toUnixTimestamp(parseDateTime64BestEffort(timestamp_utc)) -
              toUnixTimestamp(toTimeZone(parseDateTime64BestEffort(timestamp_utc), user_timezone)), 0) AS timezone_offset_seconds,
        -- Determine if transaction occurred during business hours in user's timezone
        CASE
            WHEN toHour(toTimeZone(parseDateTime64BestEffort(timestamp_utc), user_timezone)) BETWEEN 9 AND 17
            THEN 'business_hours'
            ELSE 'off_hours'
        END AS time_period,
        COUNT(*) AS transaction_count,
        SUM(amount) AS total_revenue,
        round(avg(amount), 2) AS avg_transaction_value,
        uniq(user_id) AS unique_customers
    FROM global_transactions
    WHERE parseDateTime64BestEffort(timestamp_utc) BETWEEN
        {{DateTime(start_time, '2025-09-18 14:00:00')}} AND
        {{DateTime(end_time, '2025-09-18 15:00:00')}}
    {\% if defined(country_filter) %}
    AND country = {{String(country_filter)}}
    {\% end %}
    GROUP BY country, user_timezone, time_period
    ORDER BY total_revenue DESC
    {\% if defined(limit) %}
    LIMIT {{Int32(limit, 20)}}
    {\% end %}

TYPE ENDPOINT

This query showcases ClickHouse®'s timezone handling capabilities:

• Server timezone reference with timezone() to know the ClickHouse® server's timezone setting
• Timezone conversion with toTimeZone() to convert server timestamps to user local time
• Timezone offset calculation to verify the difference between server and user timezones
• Business hours detection using timezone-aware time calculations in the user's local timezone
• Cross-timezone aggregation ensuring data is properly grouped by regional time periods

Deploy and query the global analytics API

Deploy the analytics pipeline:

tb --cloud deploy

Query regional performance metrics with sub-50ms latency across millions of transactions:

# Get timezone-aware revenue analytics for specific regions and time windows
curl "https://api.tinybird.co/v0/pipes/timezone_revenue_analytics.json?start_time=2025-09-18%2014:00:00&end_time=2025-09-18%2015:00:00&country_filter=US&timezone_filter=America/New_York&limit=20&token=YOUR_TOKEN"

The API returns comprehensive timezone-intelligent revenue analytics:

{
  "data": [
    {
      "country": "US",
      "user_timezone": "America/New_York",
      "server_timezone": "UTC",
      "local_transaction_time": "2025-09-18 10:32:45",
      "timezone_offset_seconds": -14400,
      "time_period": "business_hours",
      "transaction_count": 1847,
      "total_revenue": 284736.89,
      "avg_transaction_value": 154.23,
      "unique_customers": 1234
    },
    {
      "country": "DE",
      "user_timezone": "Europe/Berlin",
      "server_timezone": "UTC",
      "local_transaction_time": "2025-09-18 16:33:12",
      "timezone_offset_seconds": 7200,
      "time_period": "off_hours",
      "transaction_count": 923,
      "total_revenue": 67845.32,
      "avg_transaction_value": 73.52,
      "unique_customers": 678
    }
  ]
}

This API demonstrates proper timezone handling by comparing the ClickHouse® server timezone (typically UTC) against user data timezones. The timezone() function helps verify the server's timezone setting, while toTimeZone() performs the actual conversion to user local time. This approach ensures accurate regional business analysis for global e-commerce operations.

Ship faster with managed ClickHouse® on Tinybird

Tinybird provides a managed analytics backend built on ClickHouse®, handling deployment, scaling, and maintenance, and offering integrated tooling for ingestion, transformation, API creation, and observability.

This setup lets developers focus on building features and APIs instead of managing database clusters and infrastructure. Get started at https://cloud.tinybird.co/signup.

Frequently Asked Questions about ClickHouse® session timezones

Does the timezone() function impact query performance?

The timezone() function returns a cached session value and doesn't perform calculations, so it has minimal impact on query performance.

Can you modify the session timezone after connecting to ClickHouse®?

The session timezone is set when you connect and cannot be changed during an active session. You need to reconnect with new timezone settings to change it.

How does daylight saving time affect active ClickHouse® sessions?

Active sessions keep their original timezone offset even when daylight saving time changes occur. The session won't adjust automatically until you reconnect.