PostgreSQL: How to Calculate Age in Days

Often in PostgreSQL you may want to calculate an age based on a date of birth in terms of days.

You can use the age() function with the following basic syntax to do so:

SELECT *,
       extract(day FROM now() - birth_date) AS days_old
FROM emp;

This particular example will return an age (in days) based on the birth date in the birth_date column of the emp table.

The following example shows how to use this syntax in practice.

Example: How to Calculate Age in Days in PostgreSQL

Suppose that we create the following table named emp that contains information about various employees at some company including their employee ID and their date of birth:

-- create table named 'products'
CREATE TABLE emp (
  id INTEGER,
  birth_date DATE
);

-- add rows to the table 
INSERT INTO emp VALUES (0001, '1994-09-16');
INSERT INTO emp VALUES (0002, '1980-09-12');
INSERT INTO emp VALUES (0003, '1999-04-05');
INSERT INTO emp VALUES (0004, '2001-09-15');
INSERT INTO emp VALUES (0005, '2004-12-18');
INSERT INTO emp VALUES (0006, '2005-11-22');

-- view all rows in table
SELECT * FROM emp;

 id | birth_date 
----+------------
  1 | 1994-09-16
  2 | 1980-09-12
  3 | 1999-04-05
  4 | 2001-09-15
  5 | 2004-12-18
  6 | 2005-11-22

We can use the following syntax to create a new column named days_old that calculates the age (in days) of each employee based on their date of birth:

SELECT *,
       extract(day FROM now() - birth_date) AS days_old
FROM emp;

 id | birth_date | days_old 
----+------------+----------
  1 | 1994-09-16 |    10980
  2 | 1980-09-12 |    16097
  3 | 1999-04-05 |     9318
  4 | 2001-09-15 |     8424
  5 | 2004-12-18 |     7234
  6 | 2005-11-22 |     6895

The resulting table contains a new column named days_old that displays the age in days of each employee, based on their birth date in the birth_date column.

Note that this article is currently being written on 10/8/2024.

As of today, we can see:

• The employee born on 9/16/1994 is 10,980 days old.
• The employee born on 9/12/1980 is 16,097 days old.
• The employee born on 4/5/1999 is 9,318 days old.

And so on.

You can use similar syntax to calculate age in days in your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select Range of Rows from Table
PostgreSQL: How to Select the Last Row per Group
PostgreSQL: How to Calculate Difference Between Rows