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.

Asked on September 1, 2020 in Sql.
Add Comment
1 Answer(s)

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) 
Answered on September 1, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.