Answer a question

I have a table called clients below are attached image

I want to select all clients whose age between 21 yr to 35 yr, where the dob are stored like so "1403830861".

I am trying below query but no result.

select * 
from clients 
where to_timestamp(dob/1000) > timestamp '2014-01-15 00:00:00' 
  and to_timestamp(dob/1000) < timestamp '2016-01-15 23:59:59';

Answers

The root cause is this:

to_timestamp(dob/1000) >  timestamp '2014-01-15 00:00:00' 

Anyone born after 2014-01-15 would only be 2 years today (December 2016).

So unless you have 2 year old people in your database, that condition won't return anything.

The shortest way to write this, is to use the age() function:

select *
from clients
where extract(year from age(to_timestamp(dob::bigint/1000))) between 21 and 35;

That will include people with age 21 or 35

If you store the DOB as a date string (and not a number string), you obviously don't need the to_timestamp() but to_date() to convert the string into a proper date:

select *
from clients
where extract(year from age(to_date(dob, 'DD-MM-YYYY'::date))) between 21 and 35;

I assumed your DOB values are stored in the format 'DD-MM-YYYY' if you are using a different format, adjust the to_date() call.

Unrelated, but: it would be much better to store the date of birth as a date column rather then a number.

And you should NEVER store numbers in varchar columns.