How to compute the difference between two timestamps in specific units using age() in ClickHouse^®

Calculating the difference between two timestamps is a common requirement when working with time series data. Many databases include functions for this purpose, but the details can be different depending on the system and the function used.

ClickHouse offers several options for computing time differences. The age() function is one of these, and it is designed for cases where only full, completed units matter.

This article explains exactly how the age() function works in ClickHouse. The following section describes what age() does and when it makes sense to use it.

What the age function does in ClickHouse

The age() function in ClickHouse returns the number of full, completed units between two timestamps. It does not provide fractional or partial values. For example, it counts how many whole years, months, days, or seconds fit entirely between a start and an end timestamp. If an interval is not fully finished, it is not included in the count.

The age() function was added in ClickHouse 23.1 to make it possible to calculate precise, fully completed units between timestamps. This is useful when business logic requires exact periods, such as how many complete months have passed in a subscription.

The age() function is used when only full units are relevant:

• Complete years: Determining a person's age
• Full months: Billing cycles and subscription periods
• Complete days: Service-level agreement tracking
• Whole seconds: Session duration measurements

If the goal is to count calendar boundaries crossed, even if not fully completed, other functions such as dateDiff() or timeDiff() are used. For fractional differences, arithmetic on DateTime64 or other specialized functions are applied, not age().

Age syntax and argument order

The age() function in ClickHouse uses a specific argument order. The first argument is the unit as a string, followed by the start timestamp and then the end timestamp. Changing the order of these arguments changes the outcome, including the sign of the result.

The basic way to call the function is:

age('unit', start_timestamp, end_timestamp)

• Unit: A quoted string, such as 'year', 'month', 'day', or 'second'
• Start timestamp: The point in time you are starting from
• End timestamp: The point in time you are measuring to

If the end_timestamp is earlier than the start_timestamp, the result will be negative and represents the number of complete units in reverse.

There is also a version of the function that takes a fourth argument:

age('unit', start_timestamp, end_timestamp, 'Time/Zone')

The time zone is a string, such as 'UTC' or 'America/New_York'. This argument controls how the function interprets the timestamps when calculating completed units across time zones.

Supported units and aliases

The age() function in ClickHouse works with a variety of time and period units. Units are written as strings and are not sensitive to letter case. Aliases are accepted for each unit.

Time-based units include several options for measuring short durations:

• nanosecond: 'nanosecond', 'ns', 'nanoseconds'
• microsecond: 'microsecond', 'us', 'μs', 'microseconds'
• millisecond: 'millisecond', 'ms', 'milliseconds'
• second: 'second', 's', 'sec', 'seconds'
• minute: 'minute', 'min', 'minutes'
• hour: 'hour', 'h', 'hr', 'hours'

Date-based units allow for calculations over longer periods:

• day: 'day', 'd', 'days'
• week: 'week', 'w', 'wk', 'weeks'
• month: 'month', 'mo', 'mon', 'months'

Period-based units are used for broader time spans:

• quarter: 'quarter', 'q', 'quarters'
• year: 'year', 'y', 'yr', 'years'

Age versus dateDiff and timeDiff

The age(), dateDiff(), and timeDiff() functions in ClickHouse each handle time differences, but they count units in different ways.

The age() function counts only the number of whole, fully completed units between two timestamps. If a unit is not fully completed, it is not included in the result.

The dateDiff() and timeDiff() functions count the number of times a calendar boundary is crossed between the start and end values. This can include cases where the unit is not fully completed, as long as the boundary is crossed.

For example, if the range is from 2021-12-29 10:00:00 to 2022-01-01 09:59:59:

• age('month', start, end): Returns 0 because no full month has elapsed
• dateDiff('month', start, end): Returns 1 because the calculation crosses from December to January

If the range is from 2000-02-29 to 2001-02-28:

• age('year', start, end): Returns 0 because it is not a complete year
• dateDiff('year', start, end): Returns 1 because the range crosses a year boundary

The age() function usually performs similarly to other built-in date and time functions in ClickHouse. The dateDiff() and timeDiff() functions can be slightly faster in some situations, especially when counting simple boundary crossings.

Full-unit calculation explained

The age() function in ClickHouse checks whether a complete time unit has fully passed between two timestamps. It counts only those units that are entirely within the range, from the start timestamp up to but not including the boundary after the end timestamp.

The calculation does not include partial units. For example, age('month', '2021-12-29', '2022-01-01') returns 0 because the time span does not cover a full month. The result from age() is always an integer; it does not return fractions like 0.1 or 0.6.

If the end timestamp occurs before the start timestamp, age() returns a negative integer. For example, age('day', '2022-01-05', '2022-01-01') returns -4 because there are four complete days in reverse. This approach means that the function always counts fully completed units, no matter the direction between the two timestamps.

Handling time zones and precision

Time zones influence how completed units between two timestamps are counted, especially when daylight saving time (DST) changes occur. The way time is stored or interpreted can shift the result of a calculation if the time zone is not handled carefully.

DateTime64 columns allow for storing timestamps with fractional seconds. The number in parentheses indicates the precision:

• DateTime64(3): stores milliseconds
• DateTime64(6): stores microseconds
• DateTime64(9): stores nanoseconds

When using the age() function, it counts only the number of whole, completed units between timestamps. With DateTime64 columns, this means the function can distinguish between very short intervals, such as the number of completed milliseconds between two times.

Storing timestamp data in UTC (Coordinated Universal Time) keeps calculations consistent, because UTC does not change for daylight saving time. When both timestamps are in UTC, using age('hour', start_utc, end_utc) counts completed hours without ambiguity.

If local time zones are used, the time zone can be included as a fourth argument, for example: age('hour', local_start, local_end, 'America/New_York'). This tells the function how to interpret the local time, including any DST changes.

Common mistakes and quick fixes

Common mistakes can occur when using the age() function in ClickHouse. Paying attention to argument order, quoting of units, and proper handling of NULL or string values can help prevent unexpected results.

Using age('month', end, start) instead of age('month', start, end) can produce negative numbers or incorrect calculations. The function counts completed units from the first timestamp to the second, so swapping them changes the direction and the result.

Writing age(month, start, end) without quotes around the unit causes ClickHouse to treat month as a column name or variable, which leads to errors or unpredictable results. The function expects the unit to be a string. Using single quotes as in age('month', start, end) provides the correct syntax.

Supplying raw string values as timestamps or letting NULL values pass into the function can result in errors or NULL results. ClickHouse requires timestamps to be in a recognized date or time type:

• String conversion: Use parseDateTime64BestEffort('2023-01-01 12:00:00') or toDateTime('2023-01-01 12:00:00')
• NULL handling: Use coalesce, as in age('day', coalesce(start, toDateTime('1970-01-01')), end)

Practical queries you will actually run

These examples illustrate common business needs using age().

This query shows how to calculate the number of complete billing periods (in months) from the date when a subscription started up to the current moment:

SELECT
  user_id,
  age('month', subscription_start, now()) AS completed_billing_months
FROM subscriptions;

This example computes the age of a user in whole years, from their birth date to today:

SELECT
  user_id,
  age('year', birth_date, today()) AS user_age_years
FROM users;

If the birth date is stored as a string, it is possible to convert it to a date type within the query:

SELECT
  user_id,
  age('year', toDate(birth_date_str), today()) AS user_age_years
FROM users_raw;

This query measures the number of complete seconds between the start and end of each session. Only sessions with an end time are included:

SELECT
  session_id,
  age('second', session_start, session_end) AS session_seconds
FROM sessions
WHERE session_end IS NOT NULL;

Exposing age results as a Tinybird API

You can use Tinybird to create an API that calculates age() results for your data in real time.

Prepare a data file in NDJSON format. For example, a file named sessions.ndjson may contain:

{"session_id": "s1", "start": "2025-01-01 10:00:00", "end": "2025-01-01 10:00:05"}
{"session_id": "s2", "start": "2025-01-01 10:00:00.250", "end": "2025-01-01 10:00:02.900"}

To upload this file as a data source using the Tinybird CLI:

tb login
tb datasource create --file sessions.ndjson --name sessions

Create a pipe file named sessions_age.pipe with the following content:

NODE query
SQL >
  %
  SELECT
    session_id,
    {\% if defined(tz) %}
      age({{String(unit)}}, parseDateTime64BestEffort(start), parseDateTime64BestEffort(end), {{String(tz)}}) AS completed_units
    {\% else %}
      age({{String(unit)}}, parseDateTime64BestEffort(start), parseDateTime64BestEffort(end)) AS completed_units
    {\% end %}
  FROM sessions
  {\% if ({{Boolean(only_finished)}}) %}
    WHERE end IS NOT NULL
  {\% end %}

This query uses parameters:

• unit (String): for example, 'second', 'millisecond', or 'minute'
• tz (Optional String): for example, 'UTC' or 'America/New_York'
• only_finished (Boolean): filters rows to include only those with a non-null end value

To deploy the endpoint to Tinybird Cloud:

tb --cloud deploy

Make a request with the desired parameters:

curl "https://api.tinybird.co/v0/pipes/sessions_age.json?token=YOUR_TINYBIRD_TOKEN&unit=second&only_finished=1&tz=UTC"

The API response includes session IDs and the computed completed_units value from the age() function for each session.

Start building with Tinybird today

Tinybird is a managed ClickHouse service that includes a hosted database as well as developer tooling, streaming ingestion, API generation and hosting, observability, and integrations to make it easier to integrate ClickHouse into your application.

To get started, create a free account at https://cloud.tinybird.co/signup.

FAQs about the age function in ClickHouse

Which ClickHouse version first included the age function?

The age() function first appeared in ClickHouse version 23.1.

How do I cast ISO 8601 strings before calling age in ClickHouse?

Several functions are available for parsing and casting ISO 8601 strings. Use parseDateTime64BestEffort('2023-08-01T12:34:56.789Z'), toDateTime('2023-08-01 12:34:56', 'UTC'), or toDate('2023-08-01') for date-only strings.

Does the age function add noticeable query latency in ClickHouse?

The age() function has performance similar to other built-in date and time functions in ClickHouse. For large datasets, overall query speed depends on good indexing, projections, and how the query is written.