SQL AGE: Date Differences as Calendar-Aware Intervals

In PostgreSQL the AGE function answers "how much time has passed" the way a human does: in years, months and days, not a raw count of days. That makes it the go-to tool for user ages, employee tenure, and how stale an order is.

Two arguments versus one

AGE has two forms. With two arguments it computes the interval between two moments, and the order is AGE(end_ts, start_ts) — end minus start.

SELECT AGE(TIMESTAMP '2024-03-01', TIMESTAMP '2021-11-15') AS gap;
-- 2 years 3 mons 14 days

With a single argument the reference point becomes current_date implicitly (midnight today), so the result drifts day by day:

SELECT AGE(TIMESTAMP '1990-06-17') AS since_birth;
-- evaluated against midnight today

Contrast that with plain subtraction. The - operator over two dates returns just a day count — useful, but it never answers "how many years."

SELECT DATE '2024-03-01' - DATE '2021-11-15' AS raw_days;  -- 837

User ages and order staleness

Take our tables. To learn how long a user has had an account, pass created_at as the single argument:

SELECT id, email, AGE(created_at) AS account_age
FROM users
ORDER BY created_at;

Order staleness works the same way. It is handy to filter on the interval directly — PostgreSQL compares an interval against a literal:

SELECT o.id, o.amount, AGE(o.created_at) AS order_age
FROM orders o
WHERE o.status = 'paid'
  AND AGE(o.created_at) > INTERVAL '90 days';

For employees AGE describes tenure cleanly when you have a hire date (here created_at stands in as the start point):

SELECT name, dept, AGE(NOW(), created_at) AS tenure
FROM employees
ORDER BY tenure DESC;

Why months are calendar-aware

The key trait of the interval that AGE returns: months are counted by the calendar, not as a fixed 30 days. PostgreSQL rolls the date forward by whole years first, then whole months, and only the remainder is expressed in days.

SELECT AGE(DATE '2024-03-31', DATE '2024-01-31') AS feb_gap;
-- 2 mons  (not 60 days, not 59)

Because of this, the same span in days can yield a different month count — February is shorter than July. That is correct for "age," but a source of surprises if you expected day arithmetic.

• AGE always normalizes the result into years, months, days.
• Calendar months are not 30 days — the length depends on the actual dates.
• For an exact day count, use date - date subtraction or EXTRACT(EPOCH ...).

Extracting years with EXTRACT

The interval prints nicely, but for comparisons and grouping you need a number. EXTRACT pulls a field out of the interval:

SELECT id, email,
       EXTRACT(YEAR FROM AGE(created_at))::int AS full_years
FROM users;

Gotcha: EXTRACT(YEAR FROM AGE(...)) returns only the years of the interval and drops the months. For a user at "2 years 11 months" you get 2, not a rounded 3. If you want completed years that is exactly right; if you want a total month count, compute years * 12 + months.

SELECT id,
       EXTRACT(YEAR  FROM AGE(created_at)) * 12
     + EXTRACT(MONTH FROM AGE(created_at)) AS total_months
FROM users;

Differences in other engines

AGE is a PostgreSQL extension with no direct standard equivalent.

• MySQL: use TIMESTAMPDIFF(YEAR, start, end) for whole years or DATEDIFF for days. There is no ready-made years-months-days interval.
• ClickHouse: offers age('year', start, end) and dateDiff('day', ...); you always state the unit explicitly, and there is no symbolic interval either.

If the code must be portable, compute years via TIMESTAMPDIFF/dateDiff, and build the friendly human-readable interval only on PostgreSQL.