PostgreSQL – Elapsed Years

One of the things that I had to do recently was develop an example iReport which would exemplify how to achieve optional variables solely through the Java code within the JRXML file. My example consisted of creating a query that could serve as an example table named people which contained the following columns:

• id
• first_name
• last_name
• title
• birth_date
• is_deleted

The Lesser Solution

Of course, since a person’s birth date is normally considered slightly more confidential than their age, I decided to calculate the user’s age in years. At first, I didn’t think there was a built-in way to do this in PostgreSQL so I decided to write an arithmetic query. Since my sample iReport should be able to run on any PostgreSQL database, I decided against writing a custom function. Instead, I did something similar to the following:

SELECT id,
  first_name,
  last_name,
  title,
  DATE_PART('year', NOW()) - DATE_PART('year', birth_date)
  - CASE TO_CHAR(NOW(), 'MMDD') < TO_CHAR(birth_date, 'MMDD')
      WHEN TRUE THEN 1
      ELSE 0
    END AS age
FROM people
WHERE is_deleted = FALSE

Assuming the people table actually contains records where is_deleted is FALSE, the above query will return those records with the ages of the people instead of their birth dates. First, I subtracted the birth_date year from the current year. Next I format both the current date and the birth_date in the MMDD format. If when comparing the resulting strings, the MMDD string for the current date precedes that format for the birth_date, this means that the additional full year calculated before needs to be removed, otherwise the current calculation will stay. For example, if today’s date is September 19, 2011 and the birth date is September 23, 1984, the first part would be 2011 – 1984 which equals 27. The second part would see if “0919” comes before “0923”. Since “0919” does come before “0923”, 1 is subtracted from the 27 to give us a total of 26 years.

The Best Solution

As I stated when I first wrote this post, I am definitely not a PostgreSQL wizard, in fact I am definitely more of a JavaScript guy than anything. Therefore @Frank helped me out by proposing a better solution which used the AGE function which produces an INTERVAL and then used the EXTRACT function to pull the year from the INTERVAL. Therefore, after using his proposed answer (which I successfully tested), the query looks like the following:

SELECT id,
  first_name,
  last_name,
  title,
  EXTRACT(year FROM age(birth_date)) AS age
FROM people
WHERE is_deleted = FALSE