PostgreSQL: Calculate Age in Days

I constantly need to know “how old is this?” Whether it’s how many days an account has been inactive, how long an employee has worked here, how many days until a deadline, or how old a customer is—calculating days between dates is something you’ll do dozens of times in any database. It’s one of the most fundamental calculations you can do in PostgreSQL, and it’s wonderfully simple.

The beauty is that PostgreSQL treats dates as numbers under the hood. When you subtract one date from another, you just get the number of days. No complex formulas, no string parsing—just pure mathematical simplicity. Let me show you exactly how to do it.

Quick Answer: Subtract Dates and Cast to Integer

To get days between two dates:

SELECT
    emp_id,
    first_name,
    hire_date,
    (CURRENT_DATE - hire_date)::INT as days_employed,
    (CURRENT_DATE - hire_date)::INT / 365 as years_employed
FROM employees
WHERE hire_date IS NOT NULL
LIMIT 5;

Returns:

emp_id | first_name | hire_date  | days_employed | years_employed
-------+------------+------------+---------------+----------------
     1 | James      | 2020-01-15 |          2257 |              6
     2 | Mary       | 2019-06-20 |          2468 |              6

That’s it. Date subtraction gives you days directly.

How It Works

When you subtract one date from another in PostgreSQL, it returns an integer representing days. The subtraction happens at the database level, so it’s incredibly fast. Divide by 365 for approximate years, or use the AGE function for exact years/months/days.

Different Approaches & When to Use Them

Approach 1: Simple Date Subtraction (Fastest)

SELECT
    event_date,
    (CURRENT_DATE - event_date)::INT as days_ago
FROM events
WHERE event_date IS NOT NULL
ORDER BY days_ago;

Best for: Getting exact day counts.

Approach 2: Days Since/Days Until

SELECT
    event_date,
    CASE
        WHEN event_date <= CURRENT_DATE
        THEN (CURRENT_DATE - event_date)::INT
        ELSE (event_date - CURRENT_DATE)::INT
    END as days_difference
FROM events;

Works for both past and future events.

Approach 3: Using AGE Function

For exact years/months/days breakdown:

SELECT
    emp_id,
    hire_date,
    AGE(CURRENT_DATE, hire_date) as age_interval,
    EXTRACT(DAY FROM AGE(CURRENT_DATE, hire_date))::INT as days_component,
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, hire_date))::INT as years_component
FROM employees;

More complex but accurate to the month.

Approach 4: Days in Ranges

Count days in specific ranges:

SELECT
    order_id,
    order_date,
    ship_date,
    (ship_date - order_date)::INT as fulfillment_days,
    CASE
        WHEN (ship_date - order_date)::INT <= 1 THEN 'Same day'
        WHEN (ship_date - order_date)::INT <= 3 THEN 'Within 3 days'
        ELSE 'Longer than 3 days'
    END as fulfillment_speed
FROM orders;

Best for: Categorizing durations.

Real-World Example: Account Activity Analysis

Find accounts inactive for 30+ days:

SELECT
    account_id,
    customer_name,
    last_login,
    (CURRENT_DATE - last_login)::INT as days_inactive,
    CASE
        WHEN (CURRENT_DATE - last_login)::INT <= 7
        THEN 'Active'
        WHEN (CURRENT_DATE - last_login)::INT <= 30
        THEN 'Moderately active'
        WHEN (CURRENT_DATE - last_login)::INT <= 90
        THEN 'Inactive'
        ELSE 'Very inactive'
    END as account_status
FROM accounts
WHERE last_login IS NOT NULL
ORDER BY days_inactive DESC;

Performance Comparison

Method Speed Use Case
Date subtraction Very Fast Just need days
AGE function Fast Need years/months/days
Manual calculation Medium Special logic

Date subtraction is the fastest because it’s a single operation.

Common Calculations

-- Days since/until
(CURRENT_DATE - past_date)::INT as days_ago
(future_date - CURRENT_DATE)::INT as days_until

-- With hours/minutes
EXTRACT(DAY FROM (ts2 - ts1))::INT as full_days
EXTRACT(EPOCH FROM (ts2 - ts1)) / 86400 as days_with_decimals

-- Age in various units
(CURRENT_DATE - birth_date)::INT / 365 as approximate_years
(CURRENT_DATE - birth_date)::INT / 7 as weeks_old
(CURRENT_DATE - birth_date)::INT as exact_days

-- Filtering by age
WHERE (CURRENT_DATE - created_date)::INT > 30
WHERE (CURRENT_DATE - created_date)::INT < 365

FAQ

Q: Why cast to INT? Can I use it without casting? A: Date subtraction returns integer in PostgreSQL, so casting is optional. But it’s explicit and clear.

Q: How do I get days including hours/minutes? A: Use timestamps and divide by 86400: (ts2 - ts1) / INTERVAL '1 day' or EXTRACT(EPOCH FROM ...) / 86400.

Q: Should I use date subtraction or AGE function? A: Use subtraction for days only (faster). Use AGE when you need years/months/days breakdown.

Q: Can I calculate partial days? A: Use timestamps, not dates: (timestamp2 - timestamp1) / INTERVAL '1 day' gives decimals.

Q: What about leap years? A: Date subtraction accounts for them automatically. Don’t divide by 365 for accuracy; use AGE instead.

Q: How do I filter for accounts older than 1 year? A: WHERE (CURRENT_DATE - created_date)::INT > 365 or WHERE AGE(CURRENT_DATE, created_date) > INTERVAL '1 year'.

Q: Performance impact of doing this on millions of rows? A: Zero impact. This is a native operation. Very fast even on huge tables.

Wrapping Up

Calculating days between dates is one of PostgreSQL’s simplest operations: just subtract. Cast to INT to remove ambiguity. For more detailed age (years/months), use AGE.

The key takeaway: Date subtraction returns integer days directly. Fast, simple, reliable.