Both AGE() and DATE_DIFF() are DuckDB functions used to calculate time differences, but they serve distinct purposes and exhibit different behaviors that are important to understand when working with temporal data.

This article explores the difference between these two functions.

AGE() Function

The AGE() function in DuckDB calculates the exact interval between two timestamp or date values, accounting for varying month lengths, leap years, and other calendar irregularities. It returns an INTERVAL type result.

SELECT AGE(DATE '2023-05-15', DATE '2022-01-10');

Result:

1 year 4 months 5 days

Key characteristics of AGE():

• Returns a complete breakdown in years, months, days, hours, minutes, and seconds
• Produces human-readable intervals
• Accounts for calendar irregularities (like February having 28 or 29 days)
• When given a single timestamp, it calculates the interval from the current date

DATE_DIFF() Function

The DATE_DIFF() function (and its synonym DATEDIFF()) calculates the difference between two timestamps in a specified unit (days, months, years, etc.). It returns an integer (BIGINT) representing the number of complete units between the timestamps.

SELECT 
    DATE_DIFF('year', DATE '2022-01-10', DATE '2023-05-15') AS years,
    DATE_DIFF('month', DATE '2022-01-10', DATE '2023-05-15') AS months,
    DATE_DIFF('day', DATE '2022-01-10', DATE '2023-05-15') AS days;

Result:

+-------+--------+------+
| years | months | days |
+-------+--------+------+
| 1     | 16     | 490  |
+-------+--------+------+

Key characteristics of DATE_DIFF():

• Returns a simple count of complete units
• Requires specifying the unit of measurement (year, month, day, hour, minute, second, etc.)
• Provides a precise numerical result rather than a formatted interval
• The first timestamp is subtracted from the second (order matters)

Summary of the Differences

Here’s a summary of the main differences between the two functions:

Feature	AGE()	DATE_DIFF()
Return Type	INTERVAL type	INTEGER type
Format	Human-readable breakdown (years, months, days)	Single numerical value
Precision Control	Fixed hierarchical format	Flexible, specified by unit parameter
Argument Order	AGE(end_timestamp, start_timestamp)	DATE_DIFF(unit, start_timestamp, end_timestamp)

When to Use Each Function

Use AGE() when:

• You need a human-readable time difference
• You want the full breakdown of the interval
• You’re displaying time differences to users

Use DATE_DIFF() when:

• You need a simple numerical difference
• You’re performing calculations with the result
• You need to measure specifically in one unit (like just days or just months)

Understanding these differences allows us to choose the appropriate function for our specific needs when working with date and time calculations in DuckDB.