PostgreSQL – AGE Function

Last Updated : 01 Aug, 2024

In PostgreSQL, the AGE() function is a powerful tool for calculating the difference between two TIMESTAMP values. This function is especially useful for determining the age of individuals or the duration between events.

Let us better understand the AGE() Function in PostgreSQL from this article.

Syntax

age(timestamp, timestamp);

Let’s analyze the above syntax:

Parameters: The function takes two arguments, both of which are TIMESTAMP values.
Operation: It subtracts the second TIMESTAMP from the first one, resulting in an interval that represents the difference between the two timestamps.

PostgreSQL AGE Function Examples

Let us take a look at some of the examples of AGE Function in PostgreSQL to better understand the concept.

Example 1: Calculating Age from a Birthdate

Here we will evaluate the age of a person whose birth date is ‘2000-01-01’ and the current date ‘2020-03-20’, through the below statement.

Query:

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

Output:

PostgreSQL AGE Function Example

Explanation: This query will return the current date and the calculated age of the individual based on the birthdate provided. The AGE() function will return an interval showing the number of years, months, and days.

Example 2: Determining Rental Durations

The below statement query to get the top 10 rentals that have the longest durations, from the ‘rental’ table of the sample database:

PostgreSQL AGE Function Example

Query:

SELECT rental_id,
         customer_id,
         age(return_date,
             rental_date) AS duration
FROM rental
WHERE return_date IS NOT NULL
ORDER BY  duration DESC 
LIMIT 10;

Output:

PostgreSQL AGE Function Example

Explanation: This query retrieves the rental ID, customer ID, and the calculated duration for each rental where the return date is available. The results are ordered by duration in descending order, showing the top 10 rentals with the longest durations.

Important Points About PostgreSQL AGE Function

If only one TIMESTAMP argument is provided, age() calculates the interval from the provided timestamp to the current date and time.

If either of the TIMESTAMP values is NULL, the result will be NULL.

age() works with TIMESTAMP, DATE, and INTERVAL data types. When using DATE values, they are implicitly cast to TIMESTAMP values with a time component of ‘00:00:00′.

The interval result is approximate when dealing with complex calendar calculations involving leap years or varying month lengths.