PostgreSQL AGE Function

The AGE() function in PostgreSQL calculates the interval between two timestamps or between a timestamp and the current date. It returns a human-readable interval showing years, months, and days.

Syntax

-- Calculate age from timestamp to current date
AGE(timestamp)

-- Calculate age between two timestamps
AGE(timestamp1, timestamp2)
-- Returns: interval (timestamp1 - timestamp2)

Official Documentation

Example usage

-- Calculate age from birth date to now
SELECT AGE('1990-05-15'::DATE) AS age;
-- Result: 35 years 7 mons 11 days

-- Calculate age between two specific dates
SELECT AGE('2025-12-26'::DATE, '1990-05-15'::DATE) AS age;
-- Result: 35 years 7 mons 11 days

-- Calculate user ages
SELECT
    username,
    birth_date,
    AGE(birth_date) AS age,
    EXTRACT(YEAR FROM AGE(birth_date)) AS years_old
FROM users;

-- Find users over a certain age
SELECT username, birth_date
FROM users
WHERE AGE(birth_date) > INTERVAL '18 years';

-- Calculate account age
SELECT
    account_id,
    created_at,
    AGE(created_at) AS account_age,
    EXTRACT(YEAR FROM AGE(created_at)) AS years_active
FROM accounts
ORDER BY created_at;

-- Subscription duration
SELECT
    subscription_id,
    start_date,
    end_date,
    AGE(end_date, start_date) AS duration
FROM subscriptions;

-- Format age nicely
SELECT
    name,
    EXTRACT(YEAR FROM AGE(birth_date))::INT || ' years old' AS age_display
FROM people;

-- Calculate age at specific point in time
SELECT
    employee_name,
    hire_date,
    AGE('2025-01-01'::DATE, hire_date) AS tenure_at_year_start
FROM employees;

Common issues

1. Interval format: AGE returns intervals in years, months, days format which may not be intuitive for calculations.
2. Month variations: Months have different lengths, so interval arithmetic can be complex.
3. Negative intervals: When timestamp1 is before timestamp2, returns negative interval.
4. Timestamp vs date: Works with both, but be aware of time zone implications with timestamps.

Best practices

1. Use EXTRACT(YEAR FROM AGE(...)) to get just the number of years.
2. For exact day differences, use simple subtraction: date1 - date2 returns integer days.
3. Combine with INTERVAL comparisons for age-based filtering.
4. Be cautious with leap years and month-end dates when using AGE for precise calculations.

Frequently Asked Questions

Q: What's the difference between AGE and simple timestamp subtraction?
A: Simple subtraction (timestamp1 - timestamp2) returns an interval in days, hours, etc. AGE returns a structured interval with years, months, and days, which is more human-readable for age calculations.

Q: How do I get just the number of years from AGE?
A: Use EXTRACT(YEAR FROM AGE(date))::INT. For example: SELECT EXTRACT(YEAR FROM AGE('1990-01-01'::DATE)) returns 35.

Q: Can AGE handle NULL values?
A: AGE returns NULL if any argument is NULL. Use COALESCE if you need a default value.

Q: How do I calculate age in total days instead of years/months/days?
A: Use simple date subtraction: CURRENT_DATE - birth_date returns the number of days as an integer.

Q: Does AGE account for leap years?
A: Yes, AGE correctly accounts for leap years and varying month lengths when calculating the interval between dates.