Introduction to PostgreSQL AGE() function

In business applications, you often have to calculate ages such as the ages of employees, and years of service of employees. In PostgreSQL, you can use theAGE() function to accomplish these tasks.

Here’s the basic syntax of theAGE() function:

AGE(timestamp,timestamp);

TheAGE() function accepts twoTIMESTAMP values. It subtracts the second argument from the first one and returns aninterval as a result.

For example:

SELECT AGE('2017-01-01','2011-06-24');

Output:

age----------------------- 5 years 6 mons 7 days

If you want to use the current date as the first argument, you can use the following form of theAGE() function:

AGE(timestamp);

For example, if someone’s birth date is2000-01-01, and the current date is2024-01-26, their age would be:

SELECT  current_date,  AGE(timestamp '2000-01-01');

Output:

current_date |       age--------------+------------------ 2024-01-26   | 24 years 25 days(1 row)

PostgreSQL AGE() function example

We’ll use the followingrental table in thesample database:

The following example uses theAGE() function to retrieve the top 10 rentals that have the longest durations:

SELECT  rental_id,  customer_id,  AGE(return_date, rental_date) AS durationFROM  rentalWHERE  return_date IS NOT NULLORDER BY  duration DESCLIMIT  10;

Output:

rental_id | customer_id |    duration-----------+-------------+-----------------      2412 |         127 | 9 days 05:59:00     14678 |         383 | 9 days 05:59:00     13947 |         218 | 9 days 05:58:00     14468 |         224 | 9 days 05:58:00      7874 |          86 | 9 days 05:58:00     11629 |         299 | 9 days 05:58:00      5738 |         187 | 9 days 05:56:00      9938 |          63 | 9 days 05:56:00     12159 |         106 | 9 days 05:55:00      3873 |         394 | 9 days 05:55:00(10 rows)

In this example, we use theAGE() function to calculate the rental duration based on the values of the rental_date andreturn_date columns.

Summary

• Use the PostgreSQLAGE() function to calculate ages.

Last updated onMarch 21, 2024

On this page

• Introduction to PostgreSQL AGE() function
• PostgreSQL AGE() function example
• Summary