Often in PostgreSQL you may want to calculate an age based on a date of birth.
You can use the age() function with the following basic syntax to do so:
SELECT *,
age(birth_date) AS age
FROM emp;
This particular example will return an age based on the birth date in the birth_date column of the emp table.
This will return something like 19 years 9 mons 21 days as a result.
If you would only like to return the number of years, then you can use the following syntax:
SELECT *,
date_part('year',age(birth_date)) AS age
FROM emp;
This will return something like 19 as a result.
The following example shows how to use this syntax in practice.
Example: How to Calculate Age from Date of Birth 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 age that calculates the age of each employee based on their date of birth:
SELECT *,
age(birth_date) AS age
FROM emp;
id | birth_date | age
----+------------+--------------------------
1 | 1994-09-16 | 30 years 22 days
2 | 1980-09-12 | 44 years 26 days
3 | 1999-04-05 | 25 years 6 mons 3 days
4 | 2001-09-15 | 23 years 23 days
5 | 2004-12-18 | 19 years 9 mons 21 days
6 | 2005-11-22 | 18 years 10 mons 16 days
We could also use the following syntax to create a new column named age that calculates the age of each employee only in years based on their date of birth:
SELECT *,
date_part('year',age(birth_date)) AS age
FROM emp;
id | birth_date | age
----+------------+-----
1 | 1994-09-16 | 30
2 | 1980-09-12 | 44
3 | 1999-04-05 | 25
4 | 2001-09-15 | 23
5 | 2004-12-18 | 19
6 | 2005-11-22 | 18
Notice that the age column only contains the employees age in years this time.
Feel free to use whichever method you prefer to calculate an age based on date of birth 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
