September 2025 Summaries

ClickHouse provides a built-in function, port(), to efficiently extract port numbers from URLs, returning a UInt16 value, with 0 indicating no explicit port is present. When dealing with URLs lacking ports, the function facilitates conditional logic to substitute default ports like 80 for HTTP and 443 for HTTPS. The guide explores URL parsing, handling edge cases such as IPv6 addresses and malformed URLs, and optimizing performance for large datasets by recommending the use of built-in functions over regex-based extraction methods. For enhanced query performance, it suggests pre-computing port values during data ingestion or using materialized views and views for reusable logic. The document also highlights the benefits of deploying port extraction logic as an API endpoint using Tinybird for flexible parameterization and infrastructure management.

ClickHouse provides the topLevelDomain() function to efficiently extract the top-level domain (TLD) from URL strings, which is useful for analyzing web traffic, building domain classification systems, and enhancing security measures by flagging suspicious domain extensions. This function returns only the domain extension, such as com, org, or uk, and handles various URL formats, including those with protocol prefixes and subdomains, by returning the rightmost part of a domain name. It gracefully handles malformed URLs by returning an empty string rather than throwing errors. The guide explains how to use topLevelDomain() in conjunction with other ClickHouse functions like domain() and cutWWW() for more comprehensive domain analysis and provides performance optimization tips for processing large URL datasets. Additionally, it introduces Tinybird, a managed ClickHouse service that facilitates the creation of APIs for analytics workloads, abstracting infrastructure management and enabling developers to deploy web analytics APIs with ease.

When applications store dates as integers or strings in formats like '20241215' or '20241215143022', ClickHouse® treats them as regular numbers, limiting functionality and efficiency. To optimize date handling, ClickHouse® offers functions to convert these numeric formats into recognized date types, enhancing query performance, unlocking specialized compression, and enabling the use of extensive date functions. The functions, including YYYYMMDDToDate(), YYYYMMDDToDate32(), YYYYMMDDhhmmssToDateTime(), and YYYYMMDDhhmmssToDateTime64(), each serve specific roles based on date range and precision needs. Conversion to proper date types not only reduces storage size by up to 50% but also facilitates better cache utilization and faster queries. The choice between Date and Date32 depends on the date range, while DateTime and DateTime64 are selected based on required precision, with the latter supporting microseconds. For efficient date parsing in large datasets, it is advised to convert dates during data ingestion and utilize pre-computed columns. Handling invalid dates can be managed using OrNull function variants, and Tinybird's managed ClickHouse® platform offers additional support for building real-time analytics APIs, simplifying infrastructure management and allowing developers to focus on feature development.

ClickHouse's toTimeZone function enables seamless conversion of DateTime values between timezones, preserving the exact event moment while altering its display according to the target timezone. This feature is pivotal for applications involving user localization, business reporting, and multi-region analytics, as it allows the representation of times in user-specific or business-specific time zones without altering the actual timestamp. The function requires constant strings for timezone parameters, leveraging the IANA timezone database for standardization, and returns the same type after conversion, maintaining data precision with DateTime64 inputs. The guide details the syntax, limitations, and best practices of using toTimeZone, emphasizing the importance of storing timestamps in UTC to prevent ambiguity and advising on performing conversions during data retrieval rather than ingestion. It also discusses performance implications, particularly regarding sorting and indexing, suggesting materialized views for frequently queried zones to optimize queries. Additionally, the text illustrates how to build a timezone-aware analytics API using ClickHouse on the Tinybird platform, offering practical steps to convert UTC timestamps to user-specific local times, enhancing user experience and providing accurate time-based analysis.

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.

In ClickHouse®, the timeZoneOf() function is essential for resolving timezone issues by extracting and displaying the timezone identifier of datetime values, which is stored in column metadata rather than individual rows. This feature is crucial for debugging and developing timezone-aware applications, especially when dealing with data across different regions. The function operates on both DateTime and DateTime64 types, always returning a string identifier. It is useful in scenarios like materialized views and APIs, where timezone consistency is vital. Best practices suggest storing datetime data in UTC to avoid discrepancies across server configurations and ensuring explicit timezone specifications during column creation. The function is deterministic, providing consistent results for identical datetime values, though variations may occur across distributed systems if server timezone settings differ. Tinybird integrates ClickHouse® functionalities, simplifying the building of real-time analytics APIs and managing infrastructure complexities while leveraging timeZoneOf() for accurate timezone handling in global datasets.

URL-encoded strings are common in web data, where special characters are converted to percent signs followed by hexadecimal codes, like %20 for spaces. ClickHouse's decodeURLComponent function reverses this encoding, transforming strings back into readable text, and is particularly useful in web analytics and API logs. The function is designed to handle standard percent-encoding and edge cases like double encoding and Unicode sequences. It can be used on individual strings or entire columns and is optimized for performance on large datasets through the use of materialized views and LowCardinality encoding. Strategies for handling URL decoding include replacing plus signs with spaces and detecting double encoding. The function is integrated into a broader data processing framework using Tinybird, which allows for real-time URL decoding, aggregation, and querying through API endpoints, providing a scalable solution for high-throughput applications. Additionally, Tinybird's managed ClickHouse service offers advantages such as fast deployments, built-in API generation, and data-as-code workflows, simplifying the development and maintenance of URL processing pipelines.

Converting dates to Unix timestamps in ClickHouse is facilitated by the toUnixTimestamp() function, which transforms Date, DateTime, or String values into integers representing seconds since the Unix epoch starting on January 1, 1970, at midnight UTC. This function is valuable for building APIs requiring epoch seconds, exporting data to systems storing timestamps as integers, or performing time-based calculations. The function accepts various input types and returns a UInt32 value, with Date and Date32 handling being straightforward, while DateTime and DateTime64 maintain precision down to seconds. String inputs must match expected datetime formats, though they perform slower than native datetime types. The function's signature allows an optional timezone parameter, crucial for consistent results, especially when session and data timezones differ. Handling time zones correctly avoids conversion errors, and storing timestamps in UTC is recommended to prevent daylight saving time issues. Pre-computing Unix timestamps in production systems, using materialized views, or default expressions can improve performance while dealing with future dates beyond 2038 necessitates using toUnixTimestamp64() due to UInt32 overflow. Tinybird provides a managed ClickHouse platform supporting real-time analytics APIs, allowing developers to focus on application features without managing infrastructure, with Unix timestamps enhancing performance through better compression, indexing, and SIMD optimizations.

In ClickHouse, converting DateTime columns to compact numeric formats like YYYYMM, YYYYMMDD, or YYYYMMDDhhmmss can significantly reduce storage costs and improve query performance, especially when handling large datasets. The functions toYYYYMM(), toYYYYMMDD(), and toYYYYMMDDhhmmss() transform date and timestamp values into integers, leading to a 30-50% reduction in storage due to better compression and more efficient partition pruning. These numeric formats not only simplify data handling by making partition pruning faster but also enhance sharding and filtering efficiency. Although converting back from numeric formats to DateTime can lose precision, particularly regarding subsecond details and time zones, the performance benefits of using numeric formats in large-scale data systems are substantial. This conversion approach is particularly advantageous for applications like log analytics, where time-based partitioning and efficient querying are critical. With Tinybird's managed ClickHouse platform, users can leverage these optimizations to build scalable and performant analytics pipelines.

ClickHouse® offers a domain() function that efficiently extracts hostnames from URLs by removing protocols, paths, query parameters, and fragments, while retaining port numbers to distinguish different services. This function is especially useful for web traffic analysis and can handle various URL formats, including protocol-less and malformed URLs, though care should be taken with data consistency. For analytics, ClickHouse® also provides domainWithoutWWW() to normalize domains by removing the "www" prefix. Optimizations such as using materialized views and the LOWCARDINALITY data type for domain storage enhance performance for large datasets. Tinybird offers a managed ClickHouse® environment, enabling the creation of real-time APIs that leverage the domain() function without direct infrastructure management, making it ideal for building scalable web analytics applications. However, users should be cautious of common pitfalls like missing protocols and non-ASCII characters in URLs to maintain robust data pipelines.

Analyzing web traffic and API logs in ClickHouse can be complicated by URLs with query parameters, which fragment identical page views into separate entries, skewing analytics and making trend analysis challenging. To address this, ClickHouse offers built-in functions such as cutQueryString(), path(), and pathFull() to clean URLs by removing query parameters, preserving the path, or retaining the full URL with query strings. The cutQueryString() function, recommended for modern ClickHouse versions, quickly strips query strings, while older versions may rely on regex alternatives. These functions improve URL grouping and analysis, particularly when used with normalization techniques like canonicalizing trailing slashes or converting paths to lowercase. Performance considerations favor using built-in functions over regex for large datasets, and URL cleaning can be applied during data ingestion or on-demand in queries, depending on specific requirements. Tinybird further enhances this process by enabling the deployment of web analytics queries as APIs, allowing for efficient and real-time data analysis without exposing the database.

The ClickHouse® timeZoneOffset() function is a powerful tool for handling the complexities of UTC offset calculations, particularly when accounting for daylight saving time (DST) changes across multiple time zones. This function utilizes the IANA timezone database to accurately compute second-based offsets by considering historical and future DST transitions. It is essential for software developers dealing with time-sensitive applications, as it ensures precise calculations that are crucial during DST transitions, preventing errors in analytics and reporting. The guide explains how to use the function's syntax, performance considerations, and practical applications, such as building timezone-aware APIs for global data applications. By leveraging timeZoneOffset(), developers can maintain accuracy in data analysis and application robustness, particularly when working with timestamps across different regions and handling high-precision data. Additionally, the guide highlights the importance of using explicit IANA timezone names to avoid ambiguities and the benefits of integrating the function into larger systems like Tinybird for efficient IoT analytics.

ClickHouse provides the toISOYear() function, enabling users to extract the ISO 8601 week-numbering year, which differs from the traditional calendar year by starting on the Monday of the first week containing at least four days of the new year. This function is crucial for generating accurate weekly reports and time series analytics, as it avoids the distortion caused by partial weeks at year boundaries. The function works with various date types, automatically handling timezone conversions, and returns a UInt16 integer representing the year. It is particularly useful for grouping data by complete weeks in business intelligence applications, ensuring consistent and meaningful time-based analyses. The article also discusses performance optimization strategies, such as using materialized columns and projections to enhance the efficiency of ISO year calculations in large datasets. Moreover, the text explains how to create production APIs using Tinybird's managed ClickHouse platform for real-time analytics, demonstrating the practical application of ISO year calculations in building robust and scalable data solutions.

The cutToFirstSignificantSubdomain() function in ClickHouse® is designed to streamline web traffic analysis by normalizing domains, effectively consolidating metrics for URLs that represent the same core domain but appear with varying subdomains. This function extracts the registrable domain while respecting complex top-level domain structures, excluding prefixes like 'www' or 'api', thus unifying analytics under a single domain identity, such as converting news.bbc.co.uk to bbc.co.uk. It handles edge cases such as IPv4 and IPv6 addresses by returning empty strings, and it efficiently processes large datasets, outperforming regex-based alternatives in speed and memory usage. The function is particularly beneficial for developers building real-time APIs and dashboards, facilitating domain-based analytics by grouping metrics by organization rather than individual subdomains, which simplifies content categorization and deduplication. Implementing this function involves creating materialized views that automatically process data, providing a robust foundation for analytics queries without the need for complex regex patterns or public suffix list maintenance. In e-commerce analytics, this function aids in tracking conversion funnels across various domains and subdomains, ensuring consistent domain normalization and enabling detailed insights into conversion rates, revenue metrics, and time-series analysis. Platforms like Tinybird offer managed ClickHouse® services, abstracting infrastructure complexities and enhancing the ease of building and deploying real-time analytics APIs that leverage this function.

ClickHouse® offers a serverTimeZone() function to determine the timezone configuration used by the server for interpreting DateTime values, which can be crucial for developers dealing with unexpected timestamp displays or misaligned data aggregations. This guide explores various methods to retrieve server timezone settings, such as using the serverTimeZone() function, the {serverTimeZone} macro, and clickhouse-client commands, while highlighting the importance of understanding the distinction between server and session timezones. It emphasizes the implications of changing timezone configurations on existing data structures, including potential impacts on partition keys, materialized views, and Daylight Saving Time transitions. The text also discusses best practices for safely modifying server timezone settings, such as updating configuration files and validating changes, and introduces the use of automated timezone checks in CI/CD pipelines. Additionally, it covers building timezone-aware APIs using Tinybird, a managed service for ClickHouse® that helps manage timezone complexities and provides a streamlined approach to handling time-based data operations.

Tinybird Code, a CLI tool tailored for real-time data engineering with ClickHouse®, has undergone significant enhancements to improve flexibility, reliability, and user experience based on feedback received since its launch. The tool now features universal file access, enabling users to read and interact with any file within a project, fostering seamless integration into full development workflows. A new "YOLO mode" streamlines complex plan execution by allowing automatic implementation without step-by-step approvals, while improved error handling and project organization enhance usability. Additionally, Tinybird Code now routes all AI commands through a unified agent for increased capability, supporting the creation of comprehensive applications beyond just data pipelines. Integration with Claude Code is also possible, combining general programming with specialized data engineering functionalities.

Tinybird has revamped its Explorations feature, enhancing its conversational interface for data analysis by emphasizing chat-based interactions and natural language processing to deliver precise SQL queries and answers. This update reintroduces Time Series as a key component, allowing users to quickly identify trends and anomalies, and reinstates the Free queries option for those who prefer working with raw SQL. The redesigned Explorations experience includes full-screen editing, a seamless switch between modes, and the ability to export results to CSV, all aimed at improving the speed and clarity of data insights. Users can now utilize @ references to directly mention data sources or pipes in conversation, and the interface automatically generates user-friendly titles for various query types, supporting a more structured and efficient data exploration process. Additionally, the integration with ClickHouse® System Tables allows engineers to delve deeper into real-time analytics, making Explorations a valuable tool for both data analysts and engineers working with Tinybird.

The age() function in ClickHouse, introduced in version 23.1, calculates the number of full, completed units between two timestamps, such as years, months, days, or seconds, without including fractional or partial values. It is particularly useful for applications that require precise period calculations, such as subscription billing cycles or service-level agreement tracking. The function requires a specific argument order, with the unit specified as a string, followed by the start and end timestamps, and optionally a time zone. Unlike dateDiff() and timeDiff(), which count calendar boundaries even if not fully crossed, age() only accounts for entirely completed units, returning integer results that can be negative if the end timestamp is earlier than the start. Time zone considerations are crucial, especially with daylight saving time changes, and using UTC is recommended for consistent results. ClickHouse supports the use of age() with DateTime64 columns for high precision in short intervals, and Tinybird can be used to create APIs that calculate age() results in real-time.

In ClickHouse®, the dateDiff() function is used to calculate the number of calendar unit boundaries crossed between two dates, such as days, months, or years, rather than measuring the total elapsed time. This approach is particularly useful in scenarios where precise calendar boundaries are important, such as cohort analysis or reporting. The function's syntax involves specifying the unit of measurement and the start and end dates, and it returns a signed integer based on the order of the dates. The function supports various units and their aliases, counting transitions between units like months or years, which can yield results different from calculating total elapsed time in hours or seconds. Additionally, optimizing performance on large datasets can involve using materialized views or LowCardinality columns for unit strings. The document also highlights potential pitfalls such as syntax errors with unquoted unit strings, reversed date orders resulting in negative values, and inconsistencies due to time zone differences. Moreover, alternatives like the toRelativeDayNum() and dateTrunc() functions are available for different types of date calculations. The Tinybird platform is mentioned as a way to simplify building APIs for date difference calculations on ClickHouse® by managing infrastructure complexities.

In ClickHouse, the DateTime64(6) data type allows for storing date and time values with microsecond precision, which is crucial for scenarios involving rapid events like financial transactions. The timestamp() function enables conversion of various input formats, such as strings and numeric timestamps, into the DateTime64(6) format, preserving microsecond detail. This function also supports adding time intervals during conversion, making it versatile for precise time adjustments. DateTime64(6) uses 64-bit integers for microsecond representation, resulting in a larger storage footprint compared to the 32-bit DateTime type, but benefits from ClickHouse's efficient compression. The article further illustrates how to leverage DateTime64(6) in high-frequency data analytics, such as building an Application Performance Monitoring API on Tinybird's platform, to achieve sub-second query latency while maintaining precise performance monitoring. It also provides guidance on handling common conversion errors, comparing the timestamp() function with toDateTime64() for precision control, and outlines best practices for migration and time rounding in ClickHouse.

In ClickHouse®, the makeDate function is a key tool for constructing a Date type from separate year, month, and day components, facilitating efficient querying, filtering, and data storage. This function requires three integer arguments and is available in two forms: a standard version for normal date ranges and another that takes a year and day of the year for leap year calculations. The function, introduced in ClickHouse® version 22.6, ensures input validation to prevent invalid dates and offers a range from 1970 to 2149, with makeDate32 extending this range further. For datetime values, makeDateTime includes time components and time zone handling. Additionally, ClickHouse® provides conversion functions like YYYYMMDDToDate for integer-formatted dates and parseDateTime for string conversions, while for ongoing data ingestion, materialized views offer automated date construction. The guide also highlights Tinybird, a platform that enhances ClickHouse® capabilities by simplifying infrastructure management, supporting time series analytics, and facilitating real-time API deployment.

Timezones significantly influence how data and timestamps are handled in ClickHouse®, impacting the interpretation and display of date and time values. Each session in ClickHouse® operates with its own timezone setting, which can be determined by the server, the client, or explicitly set per session, enabling users to better understand the time-related functionalities of the database. The timezone() function is a straightforward method to identify the active timezone for a session, returning the IANA timezone name used for timestamp conversion. Internally, ClickHouse® stores timestamps as UTC to optimize storage and allow flexible timezone display based on session settings. Users can set or modify their session timezones through various methods depending on their connection type, and ClickHouse® provides functions like toTimeZone() for converting timestamps to different timezones without altering the underlying data. Additionally, ClickHouse® supports column-level timezone specifications and maintains a comprehensive list of supported timezones for user reference. Proper timezone management is crucial for operations such as building a global e-commerce analytics API, where accurate cross-regional analysis depends on effective timezone handling between server and user data. Tinybird, a managed ClickHouse® service, facilitates the development of high-performance APIs, ensuring accurate regional analytics by comparing server and user timezones, utilizing timezone functions for conversion and business hours detection, and supporting real-time data ingestion and processing.

Tinybird's decision to maintain a private fork of the ClickHouse® database stems from a need for greater control over their roadmap, performance, and innovation to better serve their customers in the real-time analytics space. Despite remaining active contributors to the ClickHouse® community, Tinybird found it necessary to address limitations and potential future changes in the open-source version that could impact their operations, such as the deprecation of zero-copy replication essential for their infrastructure. The private fork allows Tinybird to prioritize customer-centric improvements, optimize build processes, and introduce exclusive features that align with their business needs, such as the packed part format, a new Backup Database engine, and UNION support for materialized views. This autonomy enables Tinybird to respond swiftly to bugs and performance issues, providing them with a competitive edge while still contributing to the broader ClickHouse® ecosystem. The challenges of maintaining this fork, including adapting the CI/CD infrastructure, have been outweighed by the benefits of flexibility, performance enhancements, and cost savings, underscoring the strategic importance of this approach for Tinybird.