Calculates how much time has passed between two moments — in years, months and days.
AGE(<later timestamp>, <earlier timestamp>)
AGE(<timestamp>)
Subtracts the second moment from the first and returns an interval in familiar units — years, months, days — rather than just a number of days. With one argument, it counts from today's date. Hence the name: it's most often used to work out a person's age or length of service.
orders
| id | created_at | amount |
|---|---|---|
| 1 | 2026-07-03 10:15:00 | 120.50 |
| 2 | 2026-07-28 18:40:00 | 80.25 |
| 3 | 2026-08-09 09:05:00 | 199.99 |
| 4 | 2026-08-21 14:30:00 | 45.00 |
| 5 | 2026-09-02 11:00:00 | 64.75 |
These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.
| age |
|---|
| 36 years 3 mons 24 days |
mons means months: PostgreSQL shows intervals with abbreviated units.
| full_years |
|---|
| 36 |
| id | created_at | time_left |
|---|---|---|
| 1 | 2026-07-03 10:15:00 | 2 mons 26 days 13:45:00 |
| 2 | 2026-07-28 18:40:00 | 2 mons 1 day 05:20:00 |
| 3 | 2026-08-09 09:05:00 | 1 mon 20 days 14:55:00 |
| 4 | 2026-08-21 14:30:00 | 1 mon 8 days 09:30:00 |
| 5 | 2026-09-02 11:00:00 | 27 days 13:00:00 |
It returns an interval, not a number. Compare it with an interval — AGE(…) > INTERVAL '18 years' — and get the number of years with EXTRACT(YEAR FROM AGE(…)).
The later moment comes first, then the earlier one. Mix them up and you'll get a negative interval: -36 years -3 mons -24 days.
Plain date subtraction is something else: DATE '2026-09-13' - DATE '1990-05-20' gives a number of days, 13265, not years and months.