SQL query to calculate age-CodePudding

I am trying to calculate someones age based off of birthday, birthMonth and birthYear. I would like to create an age column and calculate their age based off the 3 other columns stated above.

Thank you

CodePudding user response：

I think you wanted a computed field to calculate the age whenever a record is inserted in your table. right? In the case of Postgres, You can extract the year from your birth_date column (assuming you have one) and then calculate the difference from the current timestamp. Most of the time you just need the year (since age).

Postgres example (date_of_birth - your column)

(date_part('year'::text, CURRENT_TIMESTAMP) - date_part('year'::text, date_of_birth))

Though the implementation might be slightly different on multiple DBMSs - the idea should work.

CodePudding user response：

Thank you everyone. I am using Navicat. I ended up going with the below query which worked

ALTER TABLE "12441_load2_all" ADD COLUMN cm_age int8;

update "12441_load2_all" set cm_age = date_part('year',age(("BirthYear"||'-'||"BirthMonth"||'-'||"BirthDay")::date)) where is_date(("BirthYear"||'-'||"BirthMonth"||'-'||"BirthDay"));