How to round timestamps in ClickHouse

ClickHouse gives you precise control over timestamp rounding through a complete set of toStartOf* functions that truncate DateTime and DateTime64 values to specific time boundaries. Each function targets a different granularity, from rounding down to the start of a day all the way to nanosecond precision.

This guide covers all seven timestamp rounding functions, their syntax differences, performance considerations, and how to avoid common precision errors when working with subsecond data.

What timestamp rounding functions exist in ClickHouse

ClickHouse gives you a complete set of toStartOf* functions that round down DateTime or DateTime64 values to the beginning of specific time units. Each function targets a different granularity, from days down to nanoseconds.

The way this works is pretty straightforward: each function truncates your timestamp to remove precision below your target unit. When you use toStartOfDay, it removes hours, minutes, and seconds. When you use toStartOfMinute, it keeps hours but removes seconds and subseconds.

toStartOfDay

The toStartOfDay function rounds any timestamp down to midnight of the same day.

SELECT toStartOfDay('2024-03-15 14:30:22') AS rounded;
-- Result: 2024-03-15 00:00:00

You can also pass an optional timezone parameter to handle day boundaries correctly across different time zones:

SELECT toStartOfDay('2024-03-15 14:30:22', 'America/New_York') AS rounded;

toStartOfHour

The toStartOfHour function removes minutes and seconds, keeping only the hour boundary.

SELECT toStartOfHour('2024-03-15 14:30:22') AS rounded;
-- Result: 2024-03-15 14:00:00

This function comes in handy for hourly aggregations in time-series analytics.

toStartOfMinute

The toStartOfMinute function preserves hours and minutes while removing seconds and any subsecond precision.

SELECT toStartOfMinute('2024-03-15 14:30:22.456') AS rounded;
-- Result: 2024-03-15 14:30:00

toStartOfSecond

The toStartOfSecond function removes subsecond precision while keeping the full seconds value.

SELECT toStartOfSecond('2024-03-15 14:30:22.456789') AS rounded;
-- Result: 2024-03-15 14:30:22

toStartOfMillisecond

The toStartOfMillisecond function works with DateTime64(3) precision and removes microsecond and nanosecond components.

SELECT toStartOfMillisecond(toDateTime64('2024-03-15 14:30:22.456789', 6)) AS rounded;
-- Result: 2024-03-15 14:30:22.456

Your input timestamp needs DateTime64 precision to contain subsecond data, otherwise you won't see any difference.

toStartOfMicrosecond

The toStartOfMicrosecond function requires DateTime64(6) precision and removes only nanosecond precision.

SELECT toStartOfMicrosecond(toDateTime64('2024-03-15 14:30:22.456789123', 9)) AS rounded;
-- Result: 2024-03-15 14:30:22.456789

toStartOfNanosecond

The toStartOfNanosecond function works with DateTime64(9) precision and essentially returns the input unchanged since nanoseconds are the highest precision available in ClickHouse.

SELECT toStartOfNanosecond(toDateTime64('2024-03-15 14:30:22.456789123', 9)) AS rounded;
-- Result: 2024-03-15 14:30:22.456789123

Syntax cheat sheet for each granularity

Here's a quick reference for all timestamp rounding functions and what they expect:

Function Signatures

All toStartOf* functions follow the same signature pattern:

toStartOfX(timestamp [, timezone])

The timestamp parameter accepts either DateTime or DateTime64 types, though subsecond functions require DateTime64 with appropriate precision.

Required and Optional Arguments

The timezone parameter becomes crucial when dealing with day boundaries or daylight saving time transitions. Without it, ClickHouse uses the server's default timezone, which might not match your data's actual timezone context.

For DateTime64 inputs, the precision has to match or exceed the target granularity. You can't round to milliseconds with a DateTime64(1) input because there's no millisecond data to work with.

Choosing between toStartOfX, toStartOfInterval, and date_trunc

ClickHouse offers three approaches for timestamp rounding, each with different trade-offs for performance and flexibility.

The toStartOf* functions provide the fastest performance for fixed intervals since they're optimized for specific time units. The toStartOfInterval function offers more flexibility for dynamic intervals but with slightly higher overhead. There's also date_trunc for SQL compatibility.

Flexibility Versus Performance

Use toStartOf* functions when you know the exact granularity at query time:

-- Fast and direct
SELECT toStartOfHour(timestamp) FROM events;

Use toStartOfInterval when the interval comes from a parameter or variable:

-- More flexible but slightly slower
SELECT toStartOfInterval(timestamp, INTERVAL 1 HOUR) FROM events;

Porting SQL From Postgres

ClickHouse includes a date_trunc function for SQL compatibility with PostgreSQL, but the native toStartOf* functions typically perform better:

-- PostgreSQL style (works but not optimal)
SELECT date_trunc('hour', timestamp) FROM events;

-- ClickHouse native (preferred)
SELECT toStartOfHour(timestamp) FROM events;

The date_trunc function essentially wraps the native functions internally, adding a small overhead that you probably won't notice in most queries.

Handling time zones and DateTime64 precision

Time zones significantly impact rounding results, especially for day and hour boundaries. ClickHouse processes timestamps in UTC by default, but your data might represent times in different zones.

DateTime64 precision determines which subsecond functions you can use effectively. Mismatched precision often leads to unexpected results or type errors.

Passing a Time Zone Parameter

Here's how timezone affects day rounding:

-- UTC interpretation
SELECT toStartOfDay('2024-03-15 02:30:22') AS utc_day;
-- Result: 2024-03-15 00:00:00

-- New York interpretation (EST/EDT)
SELECT toStartOfDay('2024-03-15 02:30:22', 'America/New_York') AS ny_day;
-- Result: 2024-03-15 05:00:00 (displayed in UTC)

The timezone parameter ensures the day boundary calculation happens in the correct local time before converting back to UTC for storage. This becomes especially important during daylight saving time transitions.

Avoiding Precision Loss

Match your DateTime64 precision to your rounding needs:

-- This works correctly
SELECT toStartOfMillisecond(toDateTime64('2024-03-15 14:30:22.456789', 6));

-- This loses precision unnecessarily
SELECT toStartOfSecond(toDateTime64('2024-03-15 14:30:22.456789', 9));

Always use the minimum precision that captures your required granularity to avoid storage overhead. Higher precision means more storage space and potentially slower queries.

Performance impact and index considerations

Timestamp rounding affects query performance in several ways, particularly around partition pruning and index utilization. The choice of rounding function can either help or hurt your query optimization.

When your table partitions align with your rounding granularity, ClickHouse can eliminate entire partitions from queries. When they don't align, you might scan more data than necessary.

Partition Pruning Effects

Here's what happens with different alignment scenarios:

-- If partitioned by day, this enables partition pruning
SELECT COUNT(*) FROM events 
WHERE toStartOfDay(timestamp) = '2024-03-15';

-- This prevents partition pruning
SELECT COUNT(*) FROM events 
WHERE toStartOfHour(timestamp) = '2024-03-15 14:00:00';

Match your rounding functions to your partitioning scheme when possible. If you partition by day but frequently query by hour, consider whether your partitioning strategy makes sense.

Benchmark Results

In general, the more specific toStartOf* functions outperform toStartOfInterval by 10-15% for equivalent operations. The performance difference becomes more pronounced with higher query volumes.

Subsecond rounding functions add minimal overhead compared to second-level rounding since they primarily manipulate the precision rather than recalculating time boundaries. The real cost comes from the DateTime64 operations themselves.

Common errors and how to fix them

Two categories of errors frequently occur when working with timestamp rounding: interval specification mistakes and type precision mismatches. Both are pretty easy to fix once you know what to look for.

Unknown Interval Unit

The toStartOfInterval function requires specific interval syntax:

-- Wrong: this will cause an error
SELECT toStartOfInterval(timestamp, '1 hour');

-- Correct: use INTERVAL keyword
SELECT toStartOfInterval(timestamp, INTERVAL 1 HOUR);

Valid interval units include SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, and YEAR. You can't use arbitrary strings or abbreviations.

Type Mismatch With DateTime64

Subsecond rounding functions expect appropriate DateTime64 precision:

-- Error: precision too low for milliseconds
SELECT toStartOfMillisecond(toDateTime64('2024-03-15 14:30:22', 1));

-- Fixed: use precision 3 or higher
SELECT toStartOfMillisecond(toDateTime64('2024-03-15 14:30:22.456', 3));

The precision number in DateTime64(n) has to be at least 3 for milliseconds, 6 for microseconds, and 9 for nanoseconds. Otherwise, the function doesn't have enough precision to work with.

Step-by-step: build a ClickHouse-based analytics API that returns rounded data

Let's create a complete example that ingests timestamped events and exposes an API endpoint returning hourly aggregated data using timestamp rounding.

This walkthrough shows how timestamp rounding integrates into real-world analytics workflows, from data ingestion through API deployment. You'll see exactly how the rounding functions work in practice.

1. Create the data source

First, create a data source to store your timestamped events:

tb datasource create events_raw \
  --format ndjson \
  --schema "timestamp DateTime64(3), user_id UInt32, event_type String, value Float64"

Then ingest some sample data:

{"timestamp": "2024-03-15 14:23:45.123", "user_id": 1001, "event_type": "click", "value": 1.0}
{"timestamp": "2024-03-15 14:45:12.456", "user_id": 1002, "event_type": "purchase", "value": 29.99}
{"timestamp": "2024-03-15 15:12:33.789", "user_id": 1001, "event_type": "click", "value": 1.0}

2. Write the pipe query

Create a pipe that aggregates events by hour using timestamp rounding:

NODE hourly_aggregation
SQL >
    SELECT 
        toStartOfHour(timestamp) AS hour,
        event_type,
        COUNT(*) AS event_count,
        SUM(value) AS total_value,
        COUNT(DISTINCT user_id) AS unique_users
    FROM events_raw
    WHERE timestamp >= now() - INTERVAL 7 DAY
    GROUP BY hour, event_type
    ORDER BY hour DESC, event_type

TYPE materialized
DATASOURCE hourly_events

The toStartOfHour function ensures all events within the same hour get grouped together, regardless of their exact timestamp. Events at 14:23:45 and 14:45:12 both round to 14:00:00.

3. Deploy and call the endpoint

Deploy your pipe to create the API endpoint:

tb --cloud deploy

Then call your new endpoint:

curl -H "Authorization: Bearer <your_token>" \
  "https://api.tinybird.co/v0/pipes/hourly_aggregation.json"

The response returns neatly aggregated data with hour boundaries:

{
  ...,
  "data": [
    {"hour": "2024-03-15 15:00:00", "event_type": "click", "event_count": 1, "total_value": 1.0, "unique_users": 1},
    {"hour": "2024-03-15 14:00:00", "event_type": "click", "event_count": 1, "total_value": 1.0, "unique_users": 1},
    {"hour": "2024-03-15 14:00:00", "event_type": "purchase", "event_count": 1, "total_value": 29.99, "unique_users": 1}
  ],
  ...
}

Ship faster with managed ClickHouse

Tinybird provides a managed ClickHouse platform that includes the timestamp rounding capabilities described above, along with developer tooling to accelerate API development. The platform manages infrastructure scaling, security, and performance optimization.

Tinybird supports use cases such as user-facing dashboards, real-time personalization, and content recommendation systems that require fast data access, while reducing operational overhead.

Sign up for free to build and test your first ClickHouse-based API in a few minutes.

FAQs about rounding timestamps in ClickHouse

Can I round timestamps when creating a materialized view?

Yes, timestamp rounding functions work perfectly in materialized view SELECT statements and often improve aggregation performance by pre-computing time buckets. This approach reduces query time since the rounding calculation happens once during ingestion rather than repeatedly during queries.

How do I apply rounding inside a window function?

Use timestamp rounding functions in the PARTITION BY or ORDER BY clauses of window functions for time-based windowing. For example: ROW_NUMBER() OVER (PARTITION BY toStartOfHour(timestamp) ORDER BY value DESC) creates row numbers within each hour bucket.

Additional resources

• toStartOfDay function
• toStartOfHour function
• toStartOfMinute function
• toStartOfSecond function
• toStartOfMillisecond function
• toStartOfMicrosecond function
• toStartOfNanosecond function
• toStartOfInterval function
• date_trunc function
• DateTime64 data type
• Time zone handling in ClickHouse
• How to convert DateTimes to a different timezone in ClickHouse
• How to handle DST-aware UTC offset calculations in ClickHouse
• How to get the current query timestamp with timezone in ClickHouse