How to compute timestamp differences with age() in ClickHouse ®

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.