Postgres age() function

Calculate the difference between timestamps or between a timestamp and the current date/time

The Postgres age() function calculates the difference between two timestamps or the difference between a timestamp and the current date and time.

This function is particularly useful for calculating ages, durations, or time intervals in various applications. For example, you can use it to determine a person's age, calculate the time elapsed since an event, or find the duration of a process or subscription.

Try it on Neon!

Neon is Serverless Postgres built for the cloud. Explore Postgres features and functions in our user-friendly SQL editor. Sign up for a free account to get started.

Function signatures

The age() function has two forms:

age(timestamp, timestamp) -> interval

This form produces an interval by subtracting the second timestamp from the first.

First argument: The end timestamp
Second argument: The start timestamp

age(timestamp) -> interval

This form subtracts the given timestamp from the timestamp for the current date (at midnight).

Example usage

Let's consider a table called employees that stores employee information, including their birth dates. We can use the age() function to calculate the age of employees.

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT,
  birth_date DATE,
  hire_date DATE
);

INSERT INTO employees (name, birth_date, hire_date) VALUES
  ('John Doe', '1985-05-15', '2010-03-01'),
  ('Jane Smith', '1990-08-22', '2015-07-10'),
  ('Bob Johnson', '1978-12-03', '2005-11-15');

SELECT
  name,
  birth_date,
  age(birth_date) AS age
FROM employees;

This query calculates the age of each employee based on their birth date.

name     | birth_date |           age
-------------+------------+-------------------------
 John Doe    | 1985-05-15 | 39 years 1 mon 10 days
 Jane Smith  | 1990-08-22 | 33 years 10 mons 3 days
 Bob Johnson | 1978-12-03 | 45 years 6 mons 22 days
(3 rows)

We can also use the age() function with two timestamps to calculate the duration of employment for each employee:

SELECT
  name,
  hire_date,
  age(CURRENT_DATE, hire_date) AS employment_duration
FROM employees;

This query calculates how long each employee has been with the company.

name     | hire_date  |   employment_duration
-------------+------------+-------------------------
 John Doe    | 2010-03-01 | 14 years 3 mons 24 days
 Jane Smith  | 2015-07-10 | 8 years 11 mons 15 days
 Bob Johnson | 2005-11-15 | 18 years 7 mons 10 days
(3 rows)

Advanced examples

Use `age()` for time-based calculations

The age() function can be useful for various time-based calculations. For example, consider a projects table that tracks the start date and deadline for projects. We can use age() to calculate project durations and remaining time:

WITH projects(name, start_date, deadline) AS (
    VALUES
        ('Project A', '2023-01-15'::DATE, '2024-06-30'::DATE),
        ('Project B', '2023-05-01'::DATE, '2023-12-31'::DATE),
        ('Project C', '2024-03-01'::DATE, '2025-02-28'::DATE)
)

SELECT
  name,
  start_date,
  deadline,
  age(deadline, start_date) AS total_duration,
  age(deadline, CURRENT_DATE) AS remaining_time
FROM projects;

This query calculates the total duration of each project and the time remaining until the deadline.

name    | start_date |  deadline  |    total_duration     |  remaining_time
-----------+------------+------------+-----------------------+------------------
 Project A | 2023-01-15 | 2024-06-30 | 1 year 5 mons 15 days | 5 days
 Project B | 2023-05-01 | 2023-12-31 | 7 mons 30 days        | -5 mons -25 days
 Project C | 2024-03-01 | 2025-02-28 | 11 mons 27 days       | 8 mons 3 days
(3 rows)

Extract specific units from age intervals

You can extract specific units of time (like years, months, or days) from the interval returned by the age() function. Here's an example that breaks down the age into years, months, and days:

WITH sample_dates(name, birth_date) AS (
  VALUES
    ('Alice', '1990-03-15'::DATE),
    ('Bob', '1985-11-30'::DATE),
    ('Charlie', '1995-07-22'::DATE)
)
SELECT
  name,
  birth_date,
  EXTRACT(YEAR FROM age(birth_date)) AS years,
  EXTRACT(MONTH FROM age(birth_date)) AS months,
  EXTRACT(DAY FROM age(birth_date)) AS days
FROM sample_dates;

This query provides a detailed breakdown of each employee's age in years, months, and days.

name   | birth_date | years | months | days
---------+------------+-------+--------+------
 Alice   | 1990-03-15 |    34 |      3 |   10
 Bob     | 1985-11-30 |    38 |      6 |   25
 Charlie | 1995-07-22 |    28 |     11 |    3
(3 rows)

Additional considerations

Negative intervals

The age() function can return negative intervals if the end timestamp is earlier than the start timestamp. Be mindful of this when using age() in calculations or comparisons.

Alternative functions

- operator — Can be used to subtract two dates or timestamps, returning an interval. This is equivalent to using the age() function with two timestamps.
current_date — Returns the current date (without the time component). Can be used with the - operator to calculate an age or duration.