PostgreSQL Calculate Age From Birthdate

There is AGE() function that performs two calculations depending of provided params:

• It substracts date2 from date1 if two params are provided:

  --calculation: date1 - date2
  age(date1, date2)

• It substracts date from the current date if only one param is provided:

  --calculation: current_date - date
  age(date)    
  

---- ## Calculate Age From Birthdate All of the following SELECT's will give the same result: SELECT EXTRACT(YEAR from AGE(birth)) as "age", * FROM customers; or SELECT EXTRACT(YEAR from AGE(NOW(), birth)) as "age", * FROM customers; or SELECT EXTRACT(YEAR from AGE(CURRENT_DATE, birth)) as "age", * FROM customers; or SELECT EXTRACT(YEAR from AGE(CURRENT_TIMESTAMP, birth)) as "age", * FROM customers; or SELECT EXTRACT(YEAR from AGE(CURRENT_TIMESTAMP, birth::timestamp)) as "age", * FROM customers; or SELECT date_part('year', AGE(birth)) as "age", * FROM customers; result: age | user_id | username | first_name | last_name |sex | birth | active ----+---------+----------+------------+-----------+----+------------+------- 27 | 1 | Hommy | Hommer | Simpson | m | 1987-04-19 | 1 27 | 2 | Marge | Marge | Simpson | f | 1987-04-19 | 1 ---- ## Add Age `CHECK` Constraint based on Birthdate Add adults age verification: my_db=# ALTER TABLE customers my_db-# ADD CONSTRAINT CHK_AGE CHECK (date_part('year', AGE(birth)) >=18); Let's try to violate it: my_db=# INSERT INTO customers (user_id, first_name, last_name, sex, birth) my_db-# VALUES (3, 'Maggie', 'Simpson', 'f', '2010-04-19'); ERROR: new row for relation "customers" violates check constraint "chk_age" ---- ## see Also * [PostgreSQL Date & Time](http://www.tutorialspoint.com/postgresql/postgresql_date_time.htm)