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';
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.
ERROR: operator does not exist: character varying / integer LINE 3: where extract(year from age(to_timestamp(dob/1000))) between... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.dob is even worse then I thought. You need to cast the string to a proper number - see my edit. Bu why on earth are you storing numbers in a varchar? Storing a date as a number is already a bad design choice, but storing a number in a varchar is even worseERROR: invalid input syntax for integer: "04-05-1991"SELECT *
FROM clients
WHERE dob BETWEEN
EXTRACT(EPOCH FROM date '2014-01-15 00:00:00') AND
EXTRACT(EPOCH FROM date '2016-01-15 23:59:59');
You can use next SQL construction
WHERE my_date_field
between to_timestamp({period_from}) and to_timestamp({period_to})
or ugly
WHERE my_date_field
BETWEEN
(TIMESTAMP 'epoch' + 1605964659 * INTERVAL '1 second')
AND
(TIMESTAMP 'epoch' + 1608556659 * INTERVAL '1 second')
DATEbetweencommand.