How to get the current query timestamp with timezone in ClickHouse^®

Getting the current timestamp in ClickHouse^® involves more than just calling now(). You need to think about timezone handling and precision requirements in production as well. The now() and now64() functions provide flexible timestamp generation with optional timezone parameters, but their behavior differs from what some developers might expect.

This guide covers the syntax variations, timezone management strategies, and practical patterns for building timestamp-dependent applications on ClickHouse^®, including how to avoid common pitfalls that can break deterministic queries and cause timezone-related bugs in production systems.

What now does in ClickHouse^®

The now() function in ClickHouse^® returns the current date and time at the moment of query analysis as a DateTime type. What makes this function special is that it's a constant expression—meaning it evaluates once at the start of query execution and returns the same value throughout the entire query.

SELECT now();
-- Returns: 2024-01-15 14:30:45

The timestamp reflects either your server's default timezone or the session's timezone if you've explicitly configured one. Since now() evaluates at query start rather than row-by-row, all references to now() within the same query will return identical values—which is exactly what you want for consistency.

Syntax and parameters of now and now64

ClickHouse^® gives you two primary functions for getting current timestamps: now([timezone]) and now64([precision], [timezone]). The basic now() function accepts an optional timezone parameter, while now64() accepts both precision and timezone parameters.

-- Basic syntax
SELECT now();
SELECT now64();

-- With timezone
SELECT now('UTC');
SELECT now64(3, 'America/New_York');

The precision parameter in now64() specifies how many decimal places you want for fractional seconds, ranging from 0 to 9. When you don't specify precision, now64() defaults to 3 decimal places, giving you millisecond precision.

Adding an explicit time zone argument

You can specify a timezone directly in the now() or now64() functions using IANA timezone identifiers. This approach converts the current UTC time to your specified timezone for display purposes.

SELECT now('America/New_York');
-- Returns: 2024-01-15 09:30:45

SELECT now('Europe/London');
-- Returns: 2024-01-15 14:30:45

SELECT now('Asia/Tokyo');
-- Returns: 2024-01-15 23:30:45

Here's what you'll commonly use:

• UTC: Coordinated Universal Time
• America/New_York: Eastern Time (US)
• Europe/London: Greenwich Mean Time/British Summer Time
• Asia/Tokyo: Japan Standard Time

The timezone argument only affects the display format—ClickHouse^® internally stores all datetime values as UTC timestamps, which keeps everything consistent under the hood.

Precision options and when to use now64

The now64() function provides sub-second precision through its first parameter, which specifies decimal places for fractional seconds. This precision becomes important when you're dealing with high-frequency operations, detailed logging, or applications where microsecond-level timestamps matter.

SELECT now64(0);    -- Second precision: 2024-01-15 14:30:45
SELECT now64(3);    -- Millisecond: 2024-01-15 14:30:45.123
SELECT now64(6);    -- Microsecond: 2024-01-15 14:30:45.123456
SELECT now64(9);    -- Nanosecond: 2024-01-15 14:30:45.123456789

You'll want now64() when you need to distinguish between events occurring within the same second, such as measuring query execution times or tracking rapid data ingestion events. For most analytical queries, though, the standard now() function provides sufficient precision.

Converting the result to epoch or string formats

ClickHouse^® timestamp functions return DateTime or DateTime64 objects, but you'll often need different formats for API responses, data exports, or integration with other systems.

ToUnixTimestamp for epoch seconds

The toUnixTimestamp() function converts datetime values to Unix epoch seconds, which many APIs and programming languages expect.

SELECT toUnixTimestamp(now());
-- Returns: 1705327845

SELECT toUnixTimestamp(now64(3));
-- Returns: 1705327845.123

This conversion is particularly useful when building REST APIs or exporting data to systems that work with epoch timestamps rather than formatted datetime strings.

Formatting with formatDateTime

Use formatDateTime() to create custom string representations of timestamps with specific formatting patterns.

SELECT formatDateTime(now(), '%Y-%m-%d %H:%M:%S');
-- Returns: 2024-01-15 14:30:45

SELECT formatDateTime(now(), '%Y-%m-%d');
-- Returns: 2024-01-15

SELECT formatDateTime(now('America/New_York'), '%I:%M %p %Z');
-- Returns: 02:30 PM EST

Common format patterns include %Y for year, %m for month, %d for day, %H for hour (24-hour), and %I for hour (12-hour).

Comparing now, now64, currentTimestamp, and toUnixTimestamp

The currentTimestamp() function serves as an alias to now() for SQL standard compatibility. Meanwhile, toUnixTimestamp() converts existing datetime values rather than generating new timestamps.

Making queries deterministic for tests and ETL

The now() function can cause headaches in automated tests and ETL pipelines because it returns different values on each execution. This non-deterministic behavior makes it difficult to create reproducible results or validate data transformations.

-- Problematic for tests
INSERT INTO events SELECT now(), 'user_action';

-- Better approach for tests
INSERT INTO events SELECT toDateTime('2024-01-15 12:00:00'), 'user_action';

For ETL processes, consider parameterizing timestamps or using fixed reference points. You might also use toDateTime() with string literals for consistent test data generation.

When building data pipelines, separating timestamp generation from business logic makes your queries more testable and predictable.

Best practices for time zones in production

Managing timezones effectively in production systems prevents data inconsistencies and reduces debugging complexity. Following practical patterns helps maintain data integrity across different geographic regions and system components.

Always store UTC in tables

Store all timestamp data in UTC to avoid timezone conversion issues during data processing and analysis. This approach provides a consistent reference point regardless of where your application or users are located.

CREATE TABLE user_events (
    user_id UInt64,
    event_time DateTime('UTC'),
    event_type String
) ENGINE = MergeTree()
ORDER BY (user_id, event_time);

-- Insert with UTC timestamp
INSERT INTO user_events VALUES (123, now('UTC'), 'login');

UTC storage simplifies data aggregation across time zones and prevents ambiguity during daylight saving time transitions.

Override TZ only in select

Apply timezone conversions only during data retrieval rather than storage. This pattern keeps your underlying data consistent while presenting localized views to different users or applications.

-- Storage: always UTC
INSERT INTO user_events SELECT user_id, now('UTC'), 'purchase';

-- Display: convert for user's timezone
SELECT user_id, toTimeZone(event_time, 'America/New_York') as local_time
FROM user_events;

This approach allows the same dataset to serve multiple geographic regions without duplicating or converting stored data.

Avoid server-level TZ drift

Server timezone configuration changes can affect query results unexpectedly, especially when now() is called without explicit timezone parameters. Document your server's timezone settings and consider using explicit timezone arguments in production queries.

-- Risky: depends on server configuration
SELECT now();

-- Safer: explicit timezone
SELECT now('UTC');

Monitor for timezone configuration changes in your deployment process, as they can cause subtle bugs in time-based calculations and data analysis.

Building ClickHouse^®-backed time series APIs with Tinybird

Tinybird's managed ClickHouse^® platform makes it easy to build time-series analytics APIs that leverage current timestamps for real-time monitoring. Here's how to create a complete IoT sensor monitoring system with timezone-aware analytics, for example.

Step 1: Create a data source for sensor readings

First, create a data source to store IoT sensor data with current timestamps. Create a sensor_readings.datasource file:

SCHEMA >
    `device_id` String,
    `sensor_type` LowCardinality(String),
    `location` String,
    `timezone` String,
    `reading_value` Float64,
    `unit` LowCardinality(String),
    `timestamp_utc` DateTime64(3, 'UTC'),
    `temperature_c` Float32,
    `humidity_percent` Float32,
    `battery_level` UInt8

ENGINE "MergeTree"
ENGINE_PARTITION_KEY "toYYYYMM(timestamp_utc)"
ENGINE_SORTING_KEY "timestamp_utc, device_id"

Deploy the data source:

tb --cloud deploy

Ingest sample IoT sensor data using the Events API:

curl -X POST \
  -H "Authorization: Bearer $TINYBIRD_TOKEN" \
  -H "Content-Type: application/json" \
  "https://api.tinybird.co/v0/events?name=sensor_readings" \
  -d '[
    {"device_id": "temp_001", "sensor_type": "temperature", "location": "New York Office", "timezone": "America/New_York", "reading_value": 22.5, "unit": "celsius", "timestamp_utc": "2024-12-01 14:30:00", "temperature_c": 22.5, "humidity_percent": 65.2, "battery_level": 87},
    {"device_id": "humid_002", "sensor_type": "humidity", "location": "London Office", "timezone": "Europe/London", "reading_value": 58.3, "unit": "percent", "timestamp_utc": "2024-12-01 14:31:00", "temperature_c": 21.1, "humidity_percent": 58.3, "battery_level": 92},
    {"device_id": "temp_003", "sensor_type": "temperature", "location": "Tokyo Office", "timezone": "Asia/Tokyo", "reading_value": 25.8, "unit": "celsius", "timestamp_utc": "2024-12-01 14:32:00", "temperature_c": 25.8, "humidity_percent": 72.1, "battery_level": 78}
  ]'

Step 2: Create real-time sensor monitoring APIs

Create a pipe that provides current sensor status with timezone-aware timestamps. Create sensor_status_api.pipe:

NODE current_sensor_status
SQL >
    %
    WITH current_time AS (
        SELECT now({\% if defined(display_timezone) %}{{String(display_timezone)}}{\% else %}'UTC'{\% end %}) AS current_timestamp
    )
    SELECT
        device_id,
        sensor_type,
        location,
        timezone AS device_timezone,
        reading_value,
        unit,
        timestamp_utc,
        -- Convert device timestamp to requested display timezone
        {\% if defined(display_timezone) %}
        toTimeZone(timestamp_utc, {{String(display_timezone)}}) AS timestamp_local,
        {{String(display_timezone)}} AS display_timezone,
        {\% else %}
        timestamp_utc AS timestamp_local,
        'UTC' AS display_timezone,
        {\% end %}
        temperature_c,
        humidity_percent,
        battery_level,
        -- Calculate time since last reading
        dateDiff('second', timestamp_utc, (SELECT current_timestamp FROM current_time)) AS seconds_since_reading,
        -- Determine device status
        CASE
            WHEN dateDiff('minute', timestamp_utc, (SELECT current_timestamp FROM current_time)) <= 5 THEN 'online'
            WHEN dateDiff('minute', timestamp_utc, (SELECT current_timestamp FROM current_time)) <= 30 THEN 'stale'
            ELSE 'offline'
        END AS device_status,
        -- Battery status
        CASE
            WHEN battery_level >= 75 THEN 'good'
            WHEN battery_level >= 25 THEN 'low'
            ELSE 'critical'
        END AS battery_status
    FROM sensor_readings
    WHERE timestamp_utc >= now() - INTERVAL 1 HOUR
    {\% if defined(device_filter) %}
      AND device_id = {{String(device_filter)}}
    {\% end %}
    {\% if defined(location_filter) %}
      AND location = {{String(location_filter)}}
    {\% end %}
    {\% if defined(sensor_type_filter) %}
      AND sensor_type = {{String(sensor_type_filter)}}
    {\% end %}
    ORDER BY timestamp_utc DESC
    {\% if defined(limit) %}
    LIMIT {{Int32(limit, 50)}}
    {\% end %}

TYPE ENDPOINT

Create a time-series analytics API for sensor trends. Create sensor_trends_api.pipe:

NODE sensor_analytics
SQL >
    %
    SELECT
        toStartOfMinute(timestamp_utc) AS time_bucket,
        {\% if defined(display_timezone) %}
        toTimeZone(toStartOfMinute(timestamp_utc), {{String(display_timezone)}}) AS time_bucket_local,
        {\% else %}
        toStartOfMinute(timestamp_utc) AS time_bucket_local,
        {\% end %}
        sensor_type,
        location,
        count() AS reading_count,
        avg(reading_value) AS avg_reading,
        min(reading_value) AS min_reading,
        max(reading_value) AS max_reading,
        avg(temperature_c) AS avg_temperature,
        avg(humidity_percent) AS avg_humidity,
        avg(battery_level) AS avg_battery_level,
        -- Time range context
        dateDiff('minute', min(timestamp_utc), now()) AS minutes_ago_start,
        dateDiff('minute', max(timestamp_utc), now()) AS minutes_ago_end
    FROM sensor_readings
    WHERE timestamp_utc >= now() - INTERVAL {{Int32(hours_back, 1)}} HOUR
    {\% if defined(location_filter) %}
      AND location = {{String(location_filter)}}
    {\% end %}
    {\% if defined(sensor_type_filter) %}
      AND sensor_type = {{String(sensor_type_filter)}}
    {\% end %}
    GROUP BY time_bucket, sensor_type, location
    ORDER BY time_bucket DESC
    {\% if defined(limit) %}
    LIMIT {{Int32(limit, 100)}}
    {\% end %}

TYPE ENDPOINT

Deploy the API endpoints:

tb --cloud deploy

Step 3: Query the real-time sensor monitoring APIs

Your timezone-aware IoT monitoring APIs are now available:

# Get current sensor status in New York timezone
curl "https://api.tinybird.co/v0/pipes/sensor_status_api.json?display_timezone=America/New_York" \
  -H "Authorization: Bearer $TINYBIRD_TOKEN"

# Monitor temperature sensors only
curl "https://api.tinybird.co/v0/pipes/sensor_status_api.json?sensor_type_filter=temperature&display_timezone=Europe/London" \
  -H "Authorization: Bearer $TINYBIRD_TOKEN"

# Get sensor trends for the last 4 hours in Tokyo timezone
curl "https://api.tinybird.co/v0/pipes/sensor_trends_api.json?hours_back=4&display_timezone=Asia/Tokyo" \
  -H "Authorization: Bearer $TINYBIRD_TOKEN"

# Analyze specific location trends
curl "https://api.tinybird.co/v0/pipes/sensor_trends_api.json?location_filter=New%20York%20Office&hours_back=2" \
  -H "Authorization: Bearer $TINYBIRD_TOKEN"

The APIs return real-time sensor analytics with timezone-aware timestamps:

{
    "meta": [
        {"name": "device_id", "type": "String"},
        {"name": "sensor_type", "type": "String"},
        {"name": "location", "type": "String"},
        {"name": "device_timezone", "type": "String"},
        {"name": "reading_value", "type": "Float64"},
        {"name": "timestamp_utc", "type": "DateTime64"},
        {"name": "timestamp_local", "type": "DateTime"},
        {"name": "display_timezone", "type": "String"},
        {"name": "seconds_since_reading", "type": "Int64"},
        {"name": "device_status", "type": "String"},
        {"name": "battery_status", "type": "String"}
    ],
    "data": [
        {
            "device_id": "temp_001",
            "sensor_type": "temperature",
            "location": "New York Office",
            "device_timezone": "America/New_York",
            "reading_value": 22.5,
            "timestamp_utc": "2024-12-01 14:30:00",
            "timestamp_local": "2024-12-01 09:30:00",
            "display_timezone": "America/New_York",
            "seconds_since_reading": 125,
            "device_status": "online",
            "battery_status": "good"
        }
    ],
    "rows": 1,
    "statistics": {
        "elapsed": 0.003,
        "rows_read": 3,
        "bytes_read": 256
    }
}

This approach provides:

• Real-time device monitoring with current timestamp calculations
• Timezone-aware displays showing sensor data in local time zones
• Device health status based on time since last reading
• Time-series analytics with configurable time ranges
• Multi-dimensional filtering by device, location, and sensor type
• Battery and connectivity monitoring for IoT device management

Your IoT sensor data becomes available as production-ready APIs that automatically handle timezone conversion and real-time status monitoring. Start building with Tinybird's free plan to create your own real-time monitoring APIs.

Next steps with Tinybird managed ClickHouse^®

Tinybird's managed ClickHouse^® service reduces the operational overhead associated with running timestamp-heavy applications at scale. Unlike self-hosted ClickHouse^®, Tinybird handles infrastructure management, scaling, and maintenance while providing developer-friendly tooling for building real-time APIs.

Whether you're building event tracking systems, IoT data pipelines, or analytics dashboards that rely on precise timestamps, Tinybird's platform lets you focus on application logic rather than database administration. The service includes built-in monitoring, automatic scaling, and is designed to achieve sub-second query latencies on large datasets under typical conditions.

Skip the infrastructure work and ship your first API in minutes with Tinybird's free tier, which includes generous usage limits for testing timestamp APIs and other real-time analytics use cases.

Additional resources

• Date and Time Functions
• DateTime Data Types
• ClickHouse^® Timezone Configuration
• Real-time analytics with billion rows at scale
• IoT monitoring with Kafka and Tinybird
• Best practices for timestamps and time zones in databases

Frequently asked questions about ClickHouse^® timestamps

Can I change the default time zone per session?

Yes, use SET timezone = 'America/New_York' to override the session timezone, but this affects all datetime operations in that session. This setting changes how datetime strings are parsed and how datetime values are displayed, but doesn't affect the internal UTC storage of timestamp data.

Does now() work inside materialized views?

The now() function works in materialized views but returns the time when the view was last refreshed, not when individual rows were processed. For row-level timestamps in materialized views, consider using a timestamp column from the source table or implementing event-time processing patterns.

Why does now() return the same value across the query?

ClickHouse^® evaluates now() once at query start for consistency, ensuring all references within the same query return identical timestamps. This behavior prevents timing inconsistencies within complex queries that might span multiple seconds to execute, maintaining data integrity in calculations and joins.