PostgreSQL AGE Function

Summary: In this tutorial, you’ll learn how to calculate the difference between two dates using the PostgreSQL AGE() function.

PostgreSQL AGE Function Overview #

The AGE() function allows you to calculate ages, such as years of service of employees.

Syntax #

AGE(end_date, start_date)

The AGE function has two parameters:

• end_date: the more recent timestamp.
• start_date: the older timestamp.

The AGE() function has another variant that accepts one argument:

AGE(start_date)

If you pass one parameter (start_date) to the AGE function, it will compare the start_date with the current date.

The AGE() function returns a value of the INTERVAL type, representing the difference between the end_date and start_date in years, months, and days.

The AGE() function returns NULL if the start_date or end_date argument is NULL.

Note that the AGE() function correctly handles leap years, ensuring proper date calculations.

Calculating the Age Between Two Dates #

The following statement uses the AGE() function to calculate the difference between January 1, 2000, and March 22, 2025:

SELECT
  AGE ('2025-03-22', '2000-01-01');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

           age
---------------------
25 years 2 mons 21 days

Calculating the Age From a Given Date #

The following statement uses the AGE() function to calculate the difference between January 1, 2000, and today:

SELECT
  AGE (TIMESTAMP '2000-01-01');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Assuming that we run this function on March 25, 2025, the output will look like the following:

           age
-------------------------
 25 years 2 mons 24 days

Calculating Age With Leap Years #

The following example uses the AGE() function with leap years:

SELECT
  AGE ('2025-03-01', '2020-02-29');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

      age
---------------------
5 years 1 day

The output indicates the AGE() function correctly handles the leap years.

Calculating Years of Service #

First, create an employees table to store employee data:

CREATE TABLE employees (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  joined_date DATE NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Second, insert some rows into the employees table:

INSERT INTO
  employees (name, joined_date)
VALUES
  ('Alex', '2022-04-10'),
  ('John', '2025-03-02'),
  ('Joe', '2023-01-15'),
  ('David', '2020-11-30'),
  ('Eve', '2022-08-05');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Third, use the AGE() function to calculate the service duration and year of service to the current date (till midnight):

SELECT
  name,
  joined_date,
  AGE (joined_date) AS service_duration,
  EXTRACT(
    YEAR
    FROM
      AGE (joined_date)
  ) AS years_of_service
FROM
  employees;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 name  | joined_date |    service_duration     | years_of_service
-------+-------------+-------------------------+------------------
 Alex  | 2022-04-10  | 2 years 11 mons 15 days |                2
 John  | 2025-03-02  | 23 days                 |                0
 Joe   | 2023-01-15  | 2 years 2 mons 10 days  |                2
 David | 2020-11-30  | 4 years 3 mons 25 days  |                4
 Eve   | 2022-08-05  | 2 years 7 mons 20 days  |                2

Note that the service duration and year of service depend on when you run the query.

Summary #

• Use the AGE() function to calculate the interval difference in years, months, and days.