Question 246947 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – POSTGRES: How to calculate age from dob in a DB that allows only select statements (no data manipulation)

My table looks like this:

Name Date of Birth
John 02-02-1990 15:30
Esther 05-05-1980 13:30
Mark 07-10-1980 12:30

I want a query that returns this.

Age Count
33 1
43 2

I’ve seen other solutions like what we have here How to calculate age (in years) based on Date of Birth and getDate()

But the db does not allow manipulation of data so only SELECT statements are allowed.

Date of birth is Date & Time data type as DD-MM-YYYY hh:mm.

I’m not very familiar with @declare but I get the following error when I try the solutions in the link:

DB engine Error
Only SELECT statements are allowed against this database.
This may be triggered by:
Issue 1022 – Database does not allow data manipulation.

Any suggestions would help. Thanks

Tags:

2

Answers


  1. 0

    Use AGE and extract the year to get the age in years:

    SELECT EXTRACT(YEAR FROM AGE(d)) AS age
     , COUNT(*)
FROM (VALUES ('John', '02-02-1990 15:30'::TIMESTAMPTZ)
           , ('Esther', '05-05-1980 13:30')
           , ('Mark', '07-10-1980 12:30')) s(name, d)
GROUP BY age
ORDER BY age;
    Login or Signup to reply.
  2. 0

    You can also calculate the age in years for each row, and then perform counts:

    SELECT
  EXTRACT(YEAR FROM CURRENT_DATE) - EXTRACT(YEAR FROM "Date of Birth") AS "Age",
  COUNT(*) AS "Count"
FROM
  your_table
GROUP BY
  "Age"
ORDER BY
  "Age";
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search