--- title: "How to retrieve current session timezone using timeZone() in ClickHouse®" excerpt: "Learn how to retrieve your current session timezone in ClickHouse® using timezone() function, manage timezone settings, and handle UTC conversions effectively." authors: "Cameron Archer" categories: "AI Resources" createdOn: "2025-09-18 16:20:55" publishedOn: "2025-09-18 16:20:55" updatedOn: "2025-09-18 16:20:55" status: "published" --- 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. ```sql 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: ```bash clickhouse-client --timezone=America/New_York ``` Or use your local timezone: ```bash clickhouse-client --use_client_time_zone ``` You can also change the timezone temporarily within a session: ```sql SET time_zone = 'Asia/Tokyo'; SELECT now(); ``` ### Using HTTP interface Add the timezone as a query parameter: ```bash curl 'http://localhost:8123/?timezone=Europe/London' -d 'SELECT now();' ``` Or include it as an HTTP header: ```bash 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: ```xml analyst Asia/Singapore ``` ## 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: ```sql 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: ```sql SELECT toDateTime('2024-09-18 10:00:00', 'Europe/London'); ``` The `toTimeZone()` function changes how an existing DateTime value is displayed: ```sql 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: ```sql SELECT * FROM system.time_zones; ``` To search for specific regions: ```sql -- 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: ```sql 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: ```plaintext ┌─────────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: ```sql 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](https://www.tinybird.co), 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](https://www.tinybird.co/blog-posts/real-time-analytics-with-billion-rows-at-scale), 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: ```json {"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](https://www.tinybird.co/docs/forward/get-data-in/events-api) to handle thousands of transactions per second globally: ```bash # 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](https://www.tinybird.co/docs/forward/work-with-data/pipes) that compares the ClickHouse® server timezone against user data timezones for accurate regional analysis: ```tinybird 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: ```bash tb --cloud deploy ``` Query regional performance metrics with sub-50ms latency across millions of transactions: ```bash # 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: ```json { "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](https://www.tinybird.co/clickhouse) 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](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.