Convert date of birth property to age during MATCH

[leelandclay]

Hello,
I have :Person nodes that have a dateOfBirth property. I need to filter out these nodes by an age range

In my mind, I'm envisioning something like:

MATCH (n:Person)
WHERE getAge(n.dateOfBirth) >= 30 AND getAge(n.dateOfBirth) <= 50
RETURN n

Is this possible? I've seen where there are models drawn up that have an "Age" property instead. I'm not a fan of that due to the requirement to scan the database for birthdays and update the age on a daily basis.

Leeland

[ameyasoft · Accepted solution]

Here is the solution. First need to get the (current date - age in years) for the minimum and maximum (like 30 and 50) and use these two dates to get all the nodes that meet your requirement.

Here is the Cypher query:

WITH date.truncate('year', date()- duration('P30Y'))- duration('P1D') AS agemin, date.truncate('year', date()- duration('P50Y'))- duration('P1D') AS agemax 
MATCH (p:Person) WHERE p.dob >= toString(agemin) OR  p.dob <= toString(agemax)
RETURN p;

agemin = "1988-12-31" and agemax = "1968-12-31". As a check I created two person nodes with the above dob and the above query fetched both nodes.

[leelandclay]

Thank you!!!! That looks awesome

[dana_canzano]

@leelandclay
looks like this might be easier when using Neo4j 3.5 forward and the use of temporal data type/functions https://neo4j.com/docs/cypher-manual/3.5/functions/temporal/duration/

for example

MATCH (p:Person) return duration.between(date(p.dob),date()).years

returns the years between 2 dates

[ameyasoft]

The problem was to determine a person's dob from age on any particular date. If a person's age is 30 years on 4/15/2009 then what's his/her dob. Also the Person node has only one property and that is dob.

MATCH (p:Person) return duration.between(date(p.dob),date()).years gives age of the selected person. Leelandclay's questions was to find out all the persons between ages of say 30 and 50.

[dana_canzano]

@ameyasoft
ok. then isnt this simply

MATCH (p:Person) 
where     duration.between(date(p.dob),date()).years>=30 
     and  duration.between(date(p.dob),date()).years<=50 
return p;

not sure i understand

The problem was to determine a person's dob from age on any particular date.

if i say my age is 20 yrs old how would you determine my date of birth given today. would it be January 1, January 2nd, January 3rd, ??? 20 years ago.

[ameyasoft]

dana.canzano:

if i say my age is 20 yrs old how would you determine my date of birth given today. would it be January 1, January 2nd, January 3rd, ??? 20 years ago.

Here is the answer:

RETURN duration.between(date("1998-12-1"),date()).years as Age1, duration.between(date("1998-06-1"),date()).years as Age2,
duration.between(date("1998-05-1"),date()).years as Age3;

Which dob you will pick? That's the answer!

[leelandclay]

So, I actually wanted a bit closer granularity than the same year of the birth. If the age range was between 30 and 50 years, I wanted to also exclude people who had not hit their 30th birthday at the time the query was ran.

Here's the code I came up with is closer. I'm going to be testing it more fully in the coming month, but this was close enough. (I'm using the CypherClient for .Net). The minMonths and maxMonths are passed in year variables that I then multiplied by 12.

.WITH("date.truncate('day', date()- duration('P" + minMonths.ToString() + "M')) AS agemax, date.truncate('day', date()- duration('P" + maxMonths.ToString() + "M')) AS agemin")
.MATCH(...)
.WHERE("other.dateOfBirth >= agemin AND other.dateOfBirth <= agemax")

[andrew_bowman]

Looks like a good approach, keep in mind you can chain these inequalities:

...
.WHERE("agemin <= other.dateOfBirth <= agemax")
...