---
title: "How to get the current query timestamp with timezone in ClickHouse®"
excerpt: "Learn how to get current timestamps with timezone support in ClickHouse® using now(), now64(), and timezone parameters for precise datetime handling."
authors: "Cameron Archer"
categories: "AI Resources"
createdOn: "2025-09-30 17:18:03"
publishedOn: "2025-09-30 17:18:03"
updatedOn: "2025-09-30 17:18:03"
status: "published"
---

Getting the current timestamp in ClickHouse{% sup %}®{% /sup %} 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{% sup %}®{% /sup %}, including how to avoid common pitfalls that can break deterministic queries and cause timezone-related bugs in production systems.

## What `now` does in ClickHouse{% sup %}®{% /sup %}

The `now()` function in ClickHouse{% sup %}®{% /sup %} 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.

```sql
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{% sup %}®{% /sup %} 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.

```sql
-- 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.

```sql
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{% sup %}®{% /sup %} 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.

```sql
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{% sup %}®{% /sup %} 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.

```sql
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.

```sql
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`

| Function | Return Type | Precision | Use Case |
| -------- | ----------- | --------- | -------- |
| `now()` | DateTime | Seconds | General queries, daily analytics |
| `now64()` | DateTime64 | Configurable (0-9) | High-frequency logging, precise timing |
| `currentTimestamp()` | DateTime | Seconds | SQL standard compatibility |
| `toUnixTimestamp()` | UInt32/Float64 | Depends on input | API responses, system integration |

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.

```sql
-- 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.

```sql
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.

```sql
-- 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.

```sql
-- 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{% sup %}®{% /sup %}-backed time series APIs with Tinybird

[Tinybird's](https://www.tinybird.co) managed ClickHouse{% sup %}®{% /sup %} 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:

```tinybird
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:

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

Ingest sample IoT sensor 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=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`:

```tinybird
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`:

```tinybird
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:

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

### Step 3: Query the real-time sensor monitoring APIs

Your timezone-aware IoT monitoring APIs are now available:

```bash
# 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:

```json
{
    "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](https://cloud.tinybird.co/signup) to create your own real-time monitoring APIs.

## Next steps with Tinybird managed ClickHouse{% sup %}®{% /sup %}

Tinybird's [managed ClickHouse{% sup %}®{% /sup %} service](https://www.tinybird.co/clickhouse) reduces the operational overhead associated with running timestamp-heavy applications at scale. Unlike self-hosted ClickHouse{% sup %}®{% /sup %}, 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](https://cloud.tinybird.co/signup) 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](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions)
- [DateTime Data Types](https://clickhouse.com/docs/en/sql-reference/data-types/datetime)
- [ClickHouse{% sup %}®{% /sup %} Timezone Configuration](https://clickhouse.com/docs/en/operations/server-configuration-parameters/settings#timezone)
- [Real-time analytics with billion rows at scale](https://www.tinybird.co/blog-posts/real-time-analytics-with-billion-rows-at-scale)
- [IoT monitoring with Kafka and Tinybird](https://www.tinybird.co/blog-posts/iot-monitoring-with-kafka-and-tinybird)
- [Best practices for timestamps and time zones in databases](https://www.tinybird.co/blog-posts/database-timestamps-timezones)

## Frequently asked questions about ClickHouse{% sup %}®{% /sup %} 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](https://tinybird.co/docs/forward/work-with-data/optimize/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{% sup %}®{% /sup %} 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.