Query for Age calculation and Date comparison inconsistent

I’m doing some counts to validate a table XXX, I designed 2 queries to calculate people younger than 18 years.

The query i’m using is the following:

select count(distinct user.id) from user
left join sometable on sometable.id = user.someTableId
left join anotherTable on sometable.anotherTableId = anotherTable.id
where (sometable.id = 'x' or user.public = true) 
AND (DATE_PART('year', age(current_date, user.birthdate)) >= 0 and DATE_PART('year', age(current_date, user.birthdate)) <= 18);

This query is giving 5000 counts (Fake result)

but this query that is supposed to do the same:

select count(distinct user.id) from user
left join sometable on sometable.id = user.someTableId
left join anotherTable on sometable.anotherTableId = anotherTable.id
where (sometable.id = 'x' or user.public = true) 
and (user.birthdate between '2002-08-26' and current_date)

SIDE NOTE: date ‘2002-08-26’ is because today is 2020-08-26, so I subtracted 18 years from today’s date.

is giving me a different count from the first one. (This last one, is giving the correct one, since is the same that I’ve in another nosql database)

I would like to know what’s the difference in the queries or why the counts are different.

Thanks in advance.

Answer

In your first query, you are including everyone who has not yet turned 19.

In your second query, you are excluding a bunch of 18 year old’s who were born prior to 2002-08-26. For example, someone born on 2002-04-12 is still 18 years old. She won’t turn 19 until 2021-04-12.

Easiest way to write in postgres is this, which provides same results as your first query:

    where extract(year from age(now(), birthdate)) <= 18

If you really want to use the format of your 2nd query, then change your line to:

    where (birth_date between '2001-08-27' and current_date)