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

Understanding timestamp management in ClickHouse involves using the now() and now64() functions, which provide flexibility in generating current timestamps with optional timezone and precision parameters. The now() function evaluates once at the start of a query, ensuring consistency across all references within the same query, while now64() allows for sub-second precision, crucial for high-frequency operations. It's important to manage timezones effectively by storing all timestamps in UTC and applying timezone conversions only during data retrieval to maintain data consistency and integrity. The toUnixTimestamp() function can convert datetime values to Unix epoch seconds, useful for APIs and exports, while formatDateTime() allows customized string representations. For deterministic queries in tests and ETL processes, fixed reference points or the toDateTime() function with string literals are recommended to avoid the non-deterministic nature of now(). The guide also highlights best practices for using timestamps in production, such as avoiding server-level timezone drift and creating timezone-aware analytics APIs using Tinybird's managed ClickHouse platform, which simplifies infrastructure management for real-time monitoring applications.