How age() Works in PostgreSQL

In Postgres, the age() function returns the age based on two timestamp or date variables.

The result is returned using years and months, rather than just days.

For example, a result might look like this: 3 years 2 mons 30 days.

Syntax

You can use one of the following two syntaxes:

age(timestamp)
age(timestamp, timestamp)

The first one returns the age based on the current date. Specifically, it subtracts from current_date() (at midnight).

The second syntax allows you to get the date based on two explicit dates. Using this syntax causes the second date to be subtracted from the first. In other words, the age is not based on today’s date – it’s based on the date provided in the first argument.

Example 1 – Use Today’s Date

Here’s an example to demonstrate the first syntax.

SELECT 	age(timestamp '1987-03-14');

Result:

33 years 2 mons 30 days

In this case, I provided a single argument. The age() function then subtracted that from today’s date (the date I ran the query) and we get the result.

Just to demonstrate this further, here it is again, but this time alongside current_date() to show the date that I ran the query.

SELECT 	
  current_date,
  age(timestamp '1987-03-14');

Result:

 current_date |           age           
--------------+-------------------------
 2020-06-13   | 33 years 2 mons 30 days

Example 2 – Use a Custom Date

In this example, I provide two dates with which to compare. Therefore, the age isn’t based on today’s date. It’s based on the first date provided to the age() function.

SELECT age(timestamp '2001-01-01', timestamp '2000-03-14');

Result:

9 mons 18 days

In this case, the age is less than a year and so the year isn’t mentioned in the results.

Here’s another example that shows what happens if only the year changes (i.e. the month and day portions of the two dates are exactly the same, but the year is different).

SELECT age(timestamp '2007-01-01', timestamp '2000-01-01');

Result:

7 years

So again, it simply omits the parts that aren’t relevant (in this case the months and days).

Example 3 – Identical Dates

Here’s what happens if both dates are the same.

SELECT age(timestamp '2001-01-01', timestamp '2001-01-01');

Result:

00:00:00

Example 4 – Negative Age

If the second date is later than the first date, you end up with a negative age.

SELECT age(timestamp '2001-01-01', timestamp '2002-03-07');

Result:

-1 years -2 mons -6 days

Here the minus sign is prepended to all components of the date (i.e. the year, month and days components).

Example 5 – Age in Years

If you only want the age in years, you can use either extract() or date_part() to extract the year from the age() function’s return value.

Here’s an example that uses the extract() function:

SELECT extract(year from age(timestamp '1997-10-25'));

Result:

22.0

That’s how it appears when I run it in Azure Data Studio.

When I run it in psql, I get this:

Both extract() and date_part() return their results using the double precision data type.

You can convert this to an integer (or other data type) if required, by appending it with ::int.

SELECT extract(year from age(timestamp '1997-10-25'))::int;

Result: