--- title: "How to extract timezone information from Datetime values in ClickHouse®" excerpt: "Learn how to extract timezone information from datetime values in ClickHouse® using the timeZoneOf function with practical examples and best practices." authors: "Cameron Archer" categories: "AI Resources" createdOn: "2025-09-30 17:11:03" publishedOn: "2025-09-30 17:11:03" updatedOn: "2025-09-30 17:11:03" status: "published" --- When you're debugging timezone issues in ClickHouse{% sup %}®{% /sup %}, the problem often isn't with your data - it's that you can't see which timezone your datetime values are actually using. The `timeZoneOf()` function solves this by extracting the timezone identifier from any datetime value, showing you exactly what timezone ClickHouse{% sup %}®{% /sup %} thinks your data lives in. This guide covers how to use `timeZoneOf()` effectively, from basic syntax to advanced scenarios like [materialized views](https://tinybird.co/docs/forward/work-with-data/optimize/materialized-views) and when publishing [API endpoints](https://tinybird.co/docs/forward/work-with-data/publish-data/endpoints) using services like Tinybird. You'll learn how to build timezone-aware applications that work reliably across different environments. ## What TimeZoneOf does and why you need it In ClickHouse{% sup %}®{% /sup %}, the timezone information for datetimes lives in the column metadata, not in the individual rows themselves. The `timeZoneOf()` function pulls that timezone identifier out and hands it back to you as a string. When you're dealing with data from users across different countries, or when you're trying to debug why your time calculations look wrong, knowing exactly which timezone your datetime column uses becomes crucial. ```sql SELECT timeZoneOf('2023-09-30 12:00:00 Europe/Madrid') -- Returns: 'Europe/Madrid' ``` For real table data, you can check any datetime column: ```sql SELECT timeZoneOf(event_time) FROM events LIMIT 1 -- Shows you the timezone that event_time column is stored in ``` ## Basic syntax and quick example The `timeZoneOf()` function works with a simple pattern: `timeZoneOf(datetime_value)`. You can pass it a string literal, a column name, or even the result of another function. Here's how it looks in practice: - **String with timezone:** `timeZoneOf('2023-01-01 12:00:00 UTC')` gives you 'UTC' - **Column reference:** `timeZoneOf(timestamp_column)` shows the column's timezone - **Function result:** `timeZoneOf(now())` reveals your server's timezone The function always returns a string containing the timezone identifier, regardless of what you pass in. ## Supported data types and return value ClickHouse{% sup %}®{% /sup %} has two main datetime types that work with `timeZoneOf()`, and both behave predictably. ### DateTime When you create a DateTime column with an explicit timezone like `DateTime('Asia/Tokyo')`, `timeZoneOf()` returns exactly that: 'Asia/Tokyo'. If you didn't specify a timezone when creating the column, you'll get whatever timezone the server was using at the time. ### DateTime64 DateTime64 columns work the same way, but with microsecond precision. Whether you use `DateTime64(3, 'Europe/London')` or `DateTime64(6, 'America/New_York')`, the precision number doesn't affect what `timeZoneOf()` returns—you still get the timezone identifier. The return value is always a string, never a number or offset. ## Server vs. column vs. literal time zones ClickHouse{% sup %}®{% /sup %} handles timezones at three different levels, and understanding this helps you predict what `timeZoneOf()` will return. | Source | When It Applies | What timeZoneOf() Returns | | ------ | ---------------- | ------------------------- | | **Server timezone** | No explicit timezone set | Whatever the server is configured to use | | **Column timezone** | Specified during table creation | The timezone you explicitly set | | **Literal timezone** | Written in the string value | The timezone from that specific string | When you create a table without mentioning timezones, ClickHouse{% sup %}®{% /sup %} falls back to the server's setting. But if you explicitly create a column with `DateTime('UTC')`, that timezone wins every time. ## Related ClickHouse{% sup %}®{% /sup %} time functions compared Several ClickHouse{% sup %}®{% /sup %} functions deal with timezones, but they serve different purposes. Knowing the difference helps you pick the right tool. ### `timezone()` helper The `timezone()` function tells you what timezone the server is using. No parameters, no datetime values - just the server's default. While `timeZoneOf()` looks at specific values or columns, `timezone()` shows the server-wide setting. ### `toTimeZone()` conversion This function actually changes your datetime values by converting them between timezones. If you have a timestamp in UTC and want to see it in Pacific time, `toTimeZone()` does the math. `timeZoneOf()` just tells you what timezone you're already working with. ### `toDateTime()` casting The `toDateTime()` function creates new datetime values, optionally with a specific timezone. It's for building datetime objects, while `timeZoneOf()` examines existing ones. ## Advanced usage and edge cases A few scenarios can trip you up when using `timeZoneOf()`, especially when dealing with the complexities of real-world timezone handling. ### Handling daylight-saving boundaries During daylight saving transitions, `timeZoneOf()` keeps returning the same timezone name even though the actual UTC offset changes. So 'America/New_York' stays 'America/New_York' whether it's currently UTC-5 or UTC-4. The function shows you the timezone identifier, not the current offset. ### Using timeZoneOf inside materialized views You can use `timeZoneOf()` in materialized view queries to create timezone-aware aggregations. This becomes handy when you want to group events by their original timezone while storing everything in a standardized format. Keep in mind that the aggregate states will be based on the value timezoneOf() at the time of materialization (ingest trigger), not at query time. ### Nullable timestamp columns When your datetime column allows NULL values, `timeZoneOf()` returns NULL for any NULL inputs. You'll want to handle this case in your queries to avoid surprises in your timezone logic. ## Common mistakes to avoid A few patterns consistently cause problems when working with `timeZoneOf()`, especially in production environments. ### 1. Forgetting explicit zone on column creation Creating datetime columns without specifying a timezone means they'll inherit the server's timezone. This might work fine in development but cause headaches when you deploy to different environments with different server settings. Always be explicit: use `DateTime('UTC')` instead of just `DateTime` when you want predictable `timeZoneOf()` results. ### 2. Mixing UTC literals with local columns Inconsistent timezone handling across your queries creates confusion. When `timeZoneOf()` returns different values for what looks like similar data, it's often because you're mixing UTC string literals with columns that use local timezones. Pick an approach and stick with it throughout your application. ### 3. Expecting deterministic output across cluster nodes In distributed ClickHouse{% sup %}®{% /sup %} setups, different nodes might have different server timezone configurations. This means the same query could return different `timeZoneOf()` results on different nodes unless you use explicit column-level timezone specifications. ## Best practices for timezone safety Following a few established patterns prevents most timezone-related issues and makes `timeZoneOf()` behavior predictable. ### Store in UTC, extract when needed The most reliable approach stores all datetime data in UTC columns, then uses `timeZoneOf()` and conversion functions for display purposes. This prevents timezone bugs while keeping the flexibility to show times in any timezone your application requires. [Learn best practices for timestamps and timezones in databases here](https://www.tinybird.co/blog-posts/database-timestamps-timezones). ### Document zone assumptions in schemas Add comments to your table schemas explaining timezone choices and document why you picked specific settings. This helps your team understand the data model and prevents confusion when `timeZoneOf()` returns unexpected values. > **Tip:** Use `COMMENT 'Stored in UTC for global consistency'` when creating datetime columns to document your timezone strategy. ## Building a timezone-aware analytics API with Tinybird Let's build a real-world example that demonstrates how `timeZoneOf()` works in practice by creating a timezone-aware analytics API. [Tinybird](https://www.tinybird.co) makes it easy to build these kinds of APIs with ClickHouse{% sup %}®{% /sup %} under the hood, showcasing how `timeZoneOf()` helps validate and debug timezone handling across global datasets. ### Step 1: Create a data source with explicit timezone columns First, create a data source that defines different datetime columns with explicit timezone specifications. Create a `global_events.datasource` file: ```tinybird SCHEMA > `event_id` String, `user_id` String, `event_time_utc` DateTime64(3, 'UTC'), `event_time_local` DateTime64(3), `user_timezone` String, `event_type` String ENGINE "MergeTree" ENGINE_PARTITION_KEY "toYYYYMM(event_time_utc)" ENGINE_SORTING_KEY "event_time_utc" ``` This schema creates two datetime columns with different timezone behaviors: - `event_time_utc`: Explicitly set to UTC timezone - `event_time_local`: Uses server timezone (will inherit from the literal timezone in the data) Deploy the data source: ```bash tb --cloud deploy ``` ### Step 2: Stream events with mixed timezone sources Now stream events from different timezone contexts using the [Tinybird Events API](https://www.tinybird.co/docs/forward/get-data-in/events-api): ```bash # Stream events with different timezone sources for timeZoneOf() demonstration curl -X POST \ -H "Authorization: Bearer $TINYBIRD_TOKEN" \ -H "Content-Type: application/json" \ "$TINYBIRD_HOST/v0/events?name=global_events" \ -d '[ {"event_id": "evt_001", "user_id": "user_1001", "event_time_utc": "2025-09-30 14:32:45", "event_time_local": "2025-09-30 10:32:45 America/New_York", "user_timezone": "America/New_York", "event_type": "login"}, {"event_id": "evt_002", "user_id": "user_1002", "event_time_utc": "2025-09-30 14:33:12", "event_time_local": "2025-09-30 15:33:12 Europe/London", "user_timezone": "Europe/London", "event_type": "purchase"}, {"event_id": "evt_003", "user_id": "user_1003", "event_time_utc": "2025-09-30 14:33:45", "event_time_local": "2025-09-30 23:33:45 Asia/Tokyo", "user_timezone": "Asia/Tokyo", "event_type": "signup"} ]' ``` The Events API appends the streaming data to your pre-defined data source, allowing us to demonstrate how `timeZoneOf()` behaves with different timezone column configurations. ### Step 3: Create a pipe that extracts and validates timezone information Create a [pipe](https://www.tinybird.co/docs/forward/work-with-data/pipes) that uses `timeZoneOf()` to extract timezone information and validate data consistency: ```tinybird NODE timezone_analysis SQL > % SELECT event_id, user_id, user_timezone, event_type, -- Core timeZoneOf() demonstration: Extract timezone from different datetime columns timeZoneOf(parseDateTime64BestEffort(event_time_utc)) AS utc_timezone, timeZoneOf(parseDateTime64BestEffort(event_time_local)) AS local_timezone, -- Show the actual datetime values alongside their timezones parseDateTime64BestEffort(event_time_utc) AS utc_timestamp, parseDateTime64BestEffort(event_time_local) AS local_timestamp, -- Validate timezone consistency by comparing stored vs expected timezones CASE WHEN timeZoneOf(parseDateTime64BestEffort(event_time_utc)) = 'UTC' THEN 'timezone_correct' ELSE 'timezone_mismatch' END AS utc_validation, CASE WHEN timeZoneOf(parseDateTime64BestEffort(event_time_local)) = user_timezone THEN 'timezone_correct' ELSE 'timezone_mismatch' END AS local_validation, -- Convert between timezones for analysis toTimeZone(parseDateTime64BestEffort(event_time_utc), user_timezone) AS utc_in_user_timezone, -- Business logic: determine if event occurred during user's business hours CASE WHEN toHour(toTimeZone(parseDateTime64BestEffort(event_time_utc), user_timezone)) BETWEEN 9 AND 17 THEN 'business_hours' ELSE 'outside_hours' END AS business_period FROM global_events WHERE parseDateTime64BestEffort(event_time_utc) >= {{DateTime(start_time, '2025-09-30 14:00:00')}} AND parseDateTime64BestEffort(event_time_utc) <= {{DateTime(end_time, '2025-09-30 15:00:00')}} {\% if defined(user_timezone_filter) %} AND user_timezone = {{String(user_timezone_filter)}} {\% end %} {\% if defined(event_type_filter) %} AND event_type = {{String(event_type_filter)}} {\% end %} ORDER BY utc_timestamp DESC {\% if defined(limit) %} LIMIT {{Int32(limit, 50)}} {\% end %} TYPE ENDPOINT ``` ### Step 4: Deploy and fetch the timezone-aware API Deploy your timezone analytics pipeline: ```bash tb --cloud deploy ``` Fetch the API to see `timeZoneOf()` in action: ```bash # Get timezone analysis for specific user timezone and event type curl "https://api.tinybird.co/v0/pipes/timezone_analysis.json?start_time=2025-09-30%2014:00:00&end_time=2025-09-30%2015:00:00&user_timezone_filter=America/New_York&event_type_filter=login&limit=20&token=READ_TOKEN" ``` The API returns detailed timezone analysis that demonstrates `timeZoneOf()` in action: ```json { "data": [ { "event_id": "evt_001", "user_id": "user_1001", "user_timezone": "America/New_York", "event_type": "login", "utc_timezone": "UTC", "local_timezone": "America/New_York", "utc_timestamp": "2025-09-30 14:32:45", "local_timestamp": "2025-09-30 10:32:45", "utc_validation": "timezone_correct", "local_validation": "timezone_correct", "utc_in_user_timezone": "2025-09-30 10:32:45", "business_period": "business_hours" }, { "event_id": "evt_002", "user_id": "user_1002", "user_timezone": "Europe/London", "event_type": "purchase", "utc_timezone": "UTC", "local_timezone": "Europe/London", "utc_timestamp": "2025-09-30 14:33:12", "local_timestamp": "2025-09-30 15:33:12", "utc_validation": "timezone_correct", "local_validation": "timezone_correct", "utc_in_user_timezone": "2025-09-30 15:33:12", "business_period": "business_hours" } ] } ``` This timezone-aware analytics API demonstrates how `timeZoneOf()` works in practice: - **Timezone extraction** using `timeZoneOf()` to identify which timezone each datetime column uses - **Data validation** comparing extracted timezones against expected values to catch inconsistencies - **Multi-timezone support** handling events from users across different global timezones - **Business logic** converting UTC events to user local time for business hour analysis - **Real-time insights** processing streaming events with sub-50ms API response times The `timeZoneOf()` function serves as a critical debugging and validation tool, helping you ensure that datetime columns contain the timezone information you expect, especially when dealing with mixed timezone data sources in global applications. ## Key takeaways and next steps with Tinybird The `timeZoneOf()` function gives you visibility into ClickHouse{% sup %}®{% /sup %}'s timezone handling, helping you debug and understand timezone-related behavior in your data pipelines. While ClickHouse{% sup %}®{% /sup %} offers powerful timezone capabilities, managing the infrastructure complexity of timezone-aware applications at scale takes significant engineering effort. [Tinybird](https://www.tinybird.co) manages the underlying ClickHouse{% sup %}®{% /sup %} infrastructure, providing access to functions like `timeZoneOf()` along with built-in API generation and real-time data ingestion. Resources are scaled within the limits of your selected plan, reducing the need for manual cluster management. [Try Tinybird for free](https://cloud.tinybird.co/signup) to quickly build real-time analytics APIs over ClickHouse{% sup %}®{% /sup %}. ## FAQs about TimeZoneOf in ClickHouse{% sup %}®{% /sup %} ### Does timeZoneOf work with DateTime64 millisecond precision? Yes, `timeZoneOf()` extracts timezone information from DateTime64 columns regardless of their precision level. Whether you use DateTime64(3) for milliseconds or DateTime64(6) for microseconds, the function returns the same timezone identifier. ### How do I list all supported time zones in ClickHouse{% sup %}®{% /sup %}? Query the `system.time_zones` table to see all available timezone identifiers: `SELECT * FROM system.time_zones`. The identifiers work with both `timeZoneOf()` and timezone conversion functions. ### Is timeZoneOf a deterministic function? Yes, `timeZoneOf()` always returns the same timezone identifier for identical datetime values and column definitions. However, results might vary across cluster nodes if server timezones differ and you're not using explicit column timezone specifications.