Compute a symbolic year-month-day interval between two timestamps.
AGE(timestamp1 [, timestamp2])## Overview Computes a symbolic INTERVAL between two timestamps, broken down into years, months, and days (plus a time-of-day component). With two arguments, returns timestamp1 minus timestamp2. With one argument, returns the interval from timestamp1 to CURRENT_DATE at midnight. Unlike subtraction of two TIMESTAMP values (which returns an interval in days and seconds only), AGE produces a human-readable breakdown that respects calendar boundaries. Use AGE for human-age calculations, tenure reporting, and anywhere you want 'X years, Y months, Z days' instead of raw day counts. ## Behavior - Returns an INTERVAL value. - Returns NULL if either argument is NULL. - Two-argument form: result = timestamp1 - timestamp2. Positive when timestamp1 is later, negative when earlier. - One-argument form: result = CURRENT_DATE - timestamp1. Effectively computes age as of today at midnight. - Result is normalized into years, months, days, and time-of-day components; extract individual fields with EXTRACT(YEAR FROM ...), EXTRACT(MONTH FROM ...), and so on. - The month breakdown is not a uniform 30-day month: it respects actual calendar month lengths between the two input dates. ## Timezone handling - When comparing TIMESTAMP WITH TIME ZONE values, the underlying instants are compared, so DST transitions do not distort the year/month breakdown. - For naive TIMESTAMP WITHOUT TIME ZONE values, the wall-clock readings are used directly. ## Compatibility - Year-month-day interval semantics. Distinct from simple TIMESTAMP subtraction, which yields a duration in days and seconds only.
| Name | Type | Description |
|---|---|---|
timestamp1 | Specifies the end (more recent) timestamp for the interval calculation. When the second argument is omitted, this is interpreted as the reference timestamp subtracted from today. | |
timestamp2 | Specifies the start (earlier) timestamp. When omitted, the function returns the interval from timestamp1 to CURRENT_DATE at midnight. |
-- Interval between two fixed timestamps
SELECT AGE(TIMESTAMP '2025-06-15', TIMESTAMP '2020-01-01') AS interval;-- Single-argument form: interval from the given date to today
SELECT AGE(TIMESTAMP '2000-03-20') AS age_today;-- Negative interval when the first argument is earlier
SELECT AGE(TIMESTAMP '2020-01-01', TIMESTAMP '2025-06-15') AS interval;-- Employee tenure as years/months/days
SELECT employee_id, AGE(CURRENT_TIMESTAMP(), hire_date::TIMESTAMP) AS tenure
FROM workforce.hr.employees;-- Extract year component from the returned interval
SELECT EXTRACT(YEAR FROM AGE(TIMESTAMP '2025-06-15', TIMESTAMP '2000-03-20')) AS tenure_years;-- Bucket open tickets by age in days
SELECT FLOOR(EXTRACT(EPOCH FROM AGE(CURRENT_TIMESTAMP(), opened_at)) / 86400) AS age_days,
COUNT(*) AS tickets
FROM support.tickets.open_tickets
GROUP BY age_days;