--- title: "How to get the current timestamp with sub-second precision in ClickHouse®" excerpt: "Learn how to use ClickHouse® now64() for sub-second timestamp precision, convert strings to DateTime64, and optimize high-resolution time queries for real-time apps." authors: "Cameron Archer" categories: "AI Resources" createdOn: "2025-09-30 17:20:23" publishedOn: "2025-10-03 10:30:00" updatedOn: "2025-10-03 10:30:00" status: "published" --- ClickHouse®'s `now64()` function returns the current timestamp with sub-second precision, allowing you to capture timing details down to nanoseconds rather than being limited to whole seconds like the standard `now()` function. This precision is useful when you're tracking high-frequency events, measuring API performance, or building real-time analytics that need to distinguish between events happening within milliseconds of each other. We'll walk through the syntax options, precision levels, and practical implementation strategies for using `now64()` in production ClickHouse® deployments, plus show you how to convert external timestamp data and optimize performance for high-throughput scenarios. ## What now64 does and why you need it The `now64()` function returns the current timestamp as a DateTime64 data type, which supports fractional seconds with customizable precision. By default, it provides millisecond precision (3 digits after the decimal point), but you can specify precision ranging from 0 (seconds) to 9 (nanoseconds). ```sql SELECT now64(); -- Returns: 2025-01-15 14:23:45.123 SELECT now64(6); -- Returns: 2025-01-15 14:23:45.123456 ``` This level of precision is useful when you're tracking high-frequency events, measuring performance with microsecond accuracy, or processing financial transactions where timing matters. Standard timestamp functions like `now()` only provide second-level precision, which isn't sufficient for modern real-time applications that need to distinguish between events happening within the same second. ## now64 syntax and precision options The basic syntax follows two patterns: `SELECT now64()` for default millisecond precision and `SELECT now64(precision)` for custom precision levels. ```sql -- Default precision (3 digits) SELECT now64(); -- Custom precision (6 digits for microseconds) SELECT now64(6); -- With timezone parameter SELECT now64(3, 'America/New_York'); ``` The precision parameter accepts integers from 0 to 9, where each number represents the decimal places after the seconds. Higher precision values capture more granular timing information but also consume more storage space and processing power. You can also specify a timezone as the second parameter, which applies the timezone offset to the returned timestamp. This is useful when your application serves users across multiple time zones or when you want to standardize timestamps to a specific region. ## Choosing the right DateTime64 precision Selecting the appropriate precision level depends on your specific use case and the trade-offs between accuracy and performance. ### 1. Nanoseconds Nanosecond precision (`now64(9)`) captures the highest level of detail available, making it suitable for high-frequency trading systems, scientific measurements, or distributed systems that need to order events with high precision. However, this level of precision comes with increased storage requirements and potential performance overhead. ### 2. Microseconds Microsecond precision (`now64(6)`) strikes a balance between detailed timing information and system efficiency. Most real-time analytics applications find this level sufficient for tracking user interactions, API response times, and event ordering across distributed systems. ### 3. Milliseconds Millisecond precision (`now64(3)`) works well for web applications, user event tracking, and general-purpose logging. This precision level provides enough granularity to distinguish between rapid user actions while maintaining optimal query performance and storage efficiency. ## Storing and querying now64 values Working with `now64()` in production involves creating appropriate table schemas and understanding how to query high-precision timestamps effectively. ### 1. Inserting into DateTime64 columns When creating tables, you'll want to define [DateTime64](https://tinybird.co/docs/sql-reference/data-types/datetime64) columns with the same precision you plan to use with `now64()`: ```sql CREATE TABLE events ( id UInt64, event_time DateTime64(6), user_id String, action String ) ENGINE = MergeTree() ORDER BY (user_id, event_time); -- Using now64() in INSERT statements INSERT INTO events VALUES (1, now64(6), 'user123', 'click'); -- Using now64() as DEFAULT value CREATE TABLE metrics ( metric_name String, value Float64, recorded_at DateTime64(3) DEFAULT now64(3) ) ENGINE = MergeTree() ORDER BY recorded_at; ``` ### 2. Selecting with sub-second filters High-precision timestamps enable you to filter data within very narrow time windows: ```sql -- Filter events within a specific microsecond range SELECT * FROM events WHERE event_time BETWEEN '2025-01-15 14:23:45.123456' AND '2025-01-15 14:23:45.123789'; -- Find events within the last 500 milliseconds SELECT * FROM events WHERE event_time >= now64(3) - INTERVAL 500 MILLISECOND; ``` ### 3. Aggregating at high resolution You can group data by sub-second intervals to analyze patterns that would be invisible with standard timestamp precision: ```sql -- Group by 100-millisecond intervals SELECT toStartOfInterval(event_time, INTERVAL 100 MILLISECOND) as time_bucket, count() as event_count FROM events WHERE event_time >= now64(3) - INTERVAL 1 HOUR GROUP BY time_bucket ORDER BY time_bucket; ``` ## Converting strings to DateTime64 in ClickHouse® Real-world applications often receive timestamp data as strings from external systems, APIs, or log files that require conversion to DateTime64 format. ### 1. Using parseDateTime64BestEffort The `parseDateTime64BestEffort` function automatically handles various timestamp formats and extracts sub-second components: ```sql -- Parse ISO 8601 with milliseconds SELECT parseDateTime64BestEffort('2025-01-15T14:23:45.123Z', 3); -- Parse with timezone offset SELECT parseDateTime64BestEffort('2025-01-15 14:23:45.123+02:00', 3); -- Handle various formats automatically SELECT parseDateTime64BestEffort('2025-01-15 14:23:45.123456', 6); ``` ### 2. Using toDateTime64 The `toDateTime64` function provides more control when converting Unix timestamps or when you know the exact format: ```sql -- Convert Unix timestamp with fractional seconds SELECT toDateTime64(1735787045.123456, 6); -- Convert string with explicit precision SELECT toDateTime64('2025-01-15 14:23:45.123', 3); -- Convert with timezone SELECT toDateTime64('1735787045.123', 3, 'UTC'); ``` Common string formats that work reliably include ISO 8601 with sub-seconds (`2025-01-02T03:04:05.678Z`), RFC 3339 variants (`2025-01-02 03:04:05.678+00:00`), and Unix epoch with fractional part (`1735787045.678`). ## now vs now64 vs toUnixTimestamp64 Understanding the differences between ClickHouse®'s timestamp functions helps you choose the right tool for each situation. ### 1. Precision comparison Each function serves different precision requirements and returns different data types: | Function | Precision | Return Type | Use Case | | -------- | --------- | ----------- | -------- | | `now()` | Seconds | DateTime | Coarse-grained timing, compatibility | | `now64(p)` | Sub-second | DateTime64(p) | High-resolution event timing | | `toUnixTimestamp64(p)` | Sub-second | Int64/Decimal | Numeric timestamps, interop | ### 2. Storage impact Higher precision levels consume more disk space and memory. DateTime64(3) uses 8 bytes per value, while DateTime64(9) requires additional storage for the extended precision. The difference becomes significant when you're storing billions of timestamps. ### 3. Typical use cases Choose `now()` for general logging where second-level precision suffices. Use `now64()` when you want to track rapid events or measure performance with sub-second accuracy. Opt for `toUnixTimestamp64()` when interfacing with systems that expect numeric Unix timestamps with fractional components. ## Handling time zones and edge cases Production deployments often involve complex timezone handling and edge cases that can affect timestamp accuracy. ### 1. Setting session time zone You can specify timezones either in the `now64()` function call or at the session level: ```sql -- Function-level timezone SELECT now64(3, 'Europe/London'); -- Session-level timezone (affects all timestamp operations) SET timezone = 'America/New_York'; SELECT now64(3); ``` Session-level timezone settings affect all timestamp operations in your query, which can be more efficient than specifying timezones in individual function calls. ### 2. Dealing with leap seconds ClickHouse® handles leap seconds by following the Unix timestamp convention, which doesn't account for leap seconds. This means that during leap second events, there might be slight discrepancies between ClickHouse® timestamps and UTC time, though this rarely affects practical applications. ## Performance tips for high-throughput timestamping Optimizing timestamp operations becomes crucial when you're processing millions of events per second. ### 1. Use low-cardinality time zones When working with multiple timezones, consider using the `LowCardinality` data type for timezone strings to reduce memory usage and improve query performance: ```sql CREATE TABLE global_events ( event_time DateTime64(3), timezone LowCardinality(String), user_id String ) ENGINE = MergeTree() ORDER BY event_time; ``` ### 2. Avoid repeated now64 calls in large inserts Instead of calling `now64()` for each row during bulk inserts, compute the timestamp once and reuse it, or use DEFAULT column expressions: ```sql -- Inefficient: calls now64() for each row INSERT INTO events SELECT id, now64(3), user_id, action FROM large_dataset; -- Better: use DEFAULT column value INSERT INTO events (id, user_id, action) SELECT id, user_id, action FROM large_dataset; ``` - **Materialized columns:** Use materialized columns or DEFAULT expressions to avoid repeated function calls during inserts - **Batch processing:** Precompute timestamps per batch in client code when inserting large datasets - **Appropriate precision:** Choose the lowest precision that meets your requirements to minimize CPU and storage overhead - **Partitioning strategy:** Leverage partitioning and primary keys on time columns for faster range queries - **Session-level settings:** Set timezone at session level to avoid per-row conversions when possible ## Building a ClickHouse®-based time series analytics API with Tinybird Let's create a complete example that demonstrates how to build a real-time API for high-precision timestamps using Tinybird's managed ClickHouse® platform. This walkthrough shows how to ingest timestamp data, process it with ClickHouse® functions, and expose it through a real-time API endpoint built with [Tinybird](https://www.tinybird.co). ### 1. Create the data source First, create a data source to store timestamp events: ```tinybird SCHEMA > `timestamp` DateTime64(6) `json:$.timestamp`, `event_type` String `json:$.event_type`, `user_id` String `json:$.user_id`, `precision_level` UInt8 `json:$.precision_level`, `timezone` String `json:$.timezone` ENGINE MergeTree ENGINE_SORTING_KEY timestamp ENGINE_PARTITION_KEY toYYYYMM(timestamp) ``` Now build the project locally: ```bash tb dev ``` This builds the project on [Tinybird Local](https://www.tinybird.co/docs/forward/install-tinybird/local) and creates your data source. Then ingest some sample data using the Events API: ```bash curl -X POST "http://localhost:7181/v0/events?name=timestamp_events" \ -H "Content-Type: application/json" \ -d '{"timestamp": "2025-01-15 14:30:15.123456", "event_type": "user_action", "user_id": "user123", "precision_level": 6, "timezone": "UTC"} {"timestamp": "2025-01-15 14:30:15.234567", "event_type": "api_call", "user_id": "user456", "precision_level": 6, "timezone": "America/New_York"} {"timestamp": "2025-01-15 14:30:15.345678", "event_type": "database_query", "user_id": "user789", "precision_level": 6, "timezone": "Europe/London"}' ``` ### 2. Create the pipe files Create a pipe that processes timestamp data with configurable precision: ```tinybird NODE timestamp_events SQL > SELECT timestamp, event_type, user_id, precision_level, timezone, now64(precision_level) AS current_timestamp, now64(precision_level, timezone) AS current_timestamp_tz, toUnixTimestamp64Micro(timestamp) AS unix_micro FROM timestamp_events WHERE timestamp >= now64(6) - INTERVAL 1 HOUR NODE timestamp_analytics SQL > % SELECT event_type, timezone, precision_level, COUNT(*) AS event_count, COUNT(DISTINCT user_id) AS unique_users, MIN(timestamp) AS first_event, MAX(timestamp) AS last_event, AVG(toUnixTimestamp64Micro(timestamp)) AS avg_unix_timestamp FROM timestamp_events WHERE {\% if defined(event_type) %} event_type = {{ String(event_type) }} {\% end %} {\% if defined(timezone) %} AND timezone = {{ String(timezone) }} {\% end %} {\% if defined(hours_back) %} AND timestamp >= now64(6) - INTERVAL {{ Int32(hours_back) }} HOUR {\% end %} GROUP BY event_type, timezone, precision_level ORDER BY event_count DESC LIMIT {{ Int32(limit, 100) }} TYPE endpoint ``` ### 3. Deploy and test the API Deploy your project to Tinybird Cloud: ```bash tb --cloud deploy ``` Then test your API endpoint: ```bash # Get all events from the last hour curl -H "Authorization: Bearer " \ "https://api.tinybird.co/v0/pipes/timestamp_analytics.json" # Filter by event type curl -H "Authorization: Bearer " \ "https://api.tinybird.co/v0/pipes/timestamp_analytics.json?event_type=user_action" # Filter by timezone and time range curl -H "Authorization: Bearer " \ "https://api.tinybird.co/v0/pipes/timestamp_analytics.json?timezone=UTC&hours_back=2" ``` ### 4. Sample API response The API returns structured data with high-precision timestamp analytics: ```json { "meta": [ { "name": "event_type", "type": "String" }, { "name": "timezone", "type": "String" }, { "name": "precision_level", "type": "UInt8" }, { "name": "event_count", "type": "UInt64" }, { "name": "unique_users", "type": "UInt64" }, { "name": "first_event", "type": "DateTime64(6)" }, { "name": "last_event", "type": "DateTime64(6)" }, { "name": "avg_unix_timestamp", "type": "Float64" } ], "data": [ { "event_type": "user_action", "timezone": "UTC", "precision_level": 6, "event_count": 1247, "unique_users": 892, "first_event": "2025-01-15 14:30:15.123456", "last_event": "2025-01-15 15:45:22.789012", "avg_unix_timestamp": 1737034215123456.0 }, { "event_type": "api_call", "timezone": "America/New_York", "precision_level": 6, "event_count": 456, "unique_users": 234, "first_event": "2025-01-15 14:30:15.234567", "last_event": "2025-01-15 15:30:18.345678", "avg_unix_timestamp": 1737034215234567.0 } ], "rows": 2, "statistics": { "elapsed": 0.045, "rows_read": 2000, "bytes_read": 89000 } } ``` ## Build real-time analytics APIs with ClickHouse® using Tinybird Managing ClickHouse® infrastructure for high-precision timestamp operations involves complexity—from optimizing storage and query performance to handling timezone edge cases and maintaining consistent precision across distributed systems. Tinybird's [managed ClickHouse® service](https://www.tinybird.co/clickhouse) reduces infrastructure overhead by providing a managed ClickHouse® environment that abstracts optimization, scaling, and maintenance. Tinybird exposes ClickHouse® features such as `now64()` and DateTime64 precision without requiring direct infrastructure management, including database clusters, performance monitoring, or version upgrades. This enables teams to focus on application development rather than database operations or configuring ClickHouse® for production workloads. Tinybird's platform supports developer efficiency through managed scaling and workflow integrations. [Sign up for a free Tinybird account](https://cloud.tinybird.co/signup) to build and test your first ClickHouse®-based API in just a few minutes. ## Additional resources - [now64 function](https://clickhouse.com/docs/en/sql-reference/functions/date-time-functions#now64) - [DateTime64 data type](https://clickhouse.com/docs/en/sql-reference/data-types/datetime64) - [toDateTime64 function](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#todatetime64) - [parseDateTime64BestEffort function](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#parsedatetime64besteffort) - [toUnixTimestamp64Micro function](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#tounixtimestamp64micro) - [How to extract URL query strings without ? or # in ClickHouse®](https://www.tinybird.co/blog-posts/extract-url-query-string-clickhouse) ## FAQs about high-precision timestamps ### Can I set a global default precision for now64? No, ClickHouse® requires specifying precision in each `now64()` call or using DEFAULT column expressions. You can standardize precision across your application by using consistent DEFAULT values in table schemas. ### Is now64 monotonic across distributed servers? The `now64()` function reflects system time on each server, so timestamps may not be perfectly monotonic across different nodes due to clock synchronization differences. Use `generateUUIDv4()` or sequence numbers if strict ordering is required.