How to filter multiple dates relative to other nodes in a query?

user_1787388 0
Viewed 75

In the Crime Investigation sandbox I'm trying to find a sequence of crimes that happened in the same location. In particular I'm trying the to find:

"Burglary" crimes that happened after "Bicycle theft" in which the Bicycle theft happened after "Drugs" crimes.

I can find the first part easily enough:

MATCH p =(a:Crime{type:'Burglary'})-[:OCCURRED_AT*2]-(b:Crime{type:'Bicycle theft'})
WHERE a.date > b.date
return p LIMIT 100;

But how would I add the second part of the question ie: "Bicycle theft" after "Drugs" crimes?

cypher neo4j
1 Answers

The problem is that you compare the dates saved in text format, and you get the wrong result:

RETURN "15/08/2017" < "9/02/2017" // => true

So your first query also gives an incorrect result. And you need to convert date field from text format to datetime. For example:

MATCH (L:Location)
MATCH p1 = (C1:Crime {type: 'Bicycle theft'})-[:OCCURRED_AT]->(L)
MATCH p2 = (C2:Crime {type: 'Burglary'})-[:OCCURRED_AT]->(L)
MATCH p3 = (C3:Crime {type: 'Drugs'})-[:OCCURRED_AT]->(L)

WITH p1, p2, p3, C1, C2, C3,
     split(C1.date, '/') as tmp1,
     split(C2.date, '/') as tmp2,
     split(C3.date, '/') as tmp3
WITH p1, p2, p3, C1, C2, C3,
     datetime(tmp1[2] + '-' + tmp1[1] + '-' + tmp1[0]) as dt1,
     datetime(tmp2[2] + '-' + tmp2[1] + '-' + tmp2[0]) as dt2,
     datetime(tmp3[2] + '-' + tmp3[1] + '-' + tmp3[0]) as dt3
WHERE dt1 < dt2 AND 
      dt1 > dt3

RETURN p1, p2, p3
user_49894600
Related