PostgresqlHow-ToBeginner · 3 min read

How to Use Age Function in PostgreSQL: Syntax and Examples

In PostgreSQL, the age() function calculates the time interval between two dates or timestamps, returning the result as an interval type. You can use age(timestamp1, timestamp2) to find the difference or simply age(timestamp) to find the difference from the current date and time.

📐

Syntax

The age() function has two main forms:

age(timestamp, timestamp): Returns the interval between the first and second timestamp.
age(timestamp): Returns the interval between the given timestamp and the current date and time.

The result is an interval showing years, months, days, hours, minutes, and seconds.

sql

age(timestamp, timestamp)
age(timestamp)

💻

Example

This example shows how to calculate the age between two dates and how to get the age from a date to now.

sql

SELECT age('2024-06-01'::timestamp, '1990-04-15'::timestamp) AS difference_between_dates,
       age('2000-01-01'::timestamp) AS age_from_date_to_now;

Output

difference_between_dates | age_from_date_to_now -------------------------+--------------------- 34 years 1 mon 17 days | 24 years 5 mons 27 days 00:00:00

⚠️

Common Pitfalls

Common mistakes when using age() include:

Passing dates as strings without casting to timestamp or date, which can cause errors or unexpected results.
Confusing the order of arguments; age(timestamp1, timestamp2) returns timestamp1 - timestamp2, so reversing them changes the sign of the interval.
Expecting age() to return a simple number instead of an interval type.

sql

/* Wrong: No casting, may cause error or wrong result */
SELECT age('2024-06-01', '1990-04-15');

/* Right: Cast to timestamp for clarity */
SELECT age('2024-06-01'::timestamp, '1990-04-15'::timestamp);

📊

Quick Reference

Usage	Description	Example
age(timestamp, timestamp)	Returns interval between two timestamps	age('2024-06-01', '1990-04-15')
age(timestamp)	Returns interval from given timestamp to now	age('2000-01-01')
Result type	Returns an interval showing years, months, days, etc.	34 years 1 mon 17 days

✅

Key Takeaways

Use age() to get the interval between two timestamps or from a timestamp to now.

Always cast string dates to timestamp or date for accurate results.

The order of arguments matters: age(t1, t2) means t1 - t2.

The result is an interval type showing years, months, days, and time components.

Use age(timestamp) to find how old a date/time is compared to the current moment.