CODEWITHSUNDEEP
neo4jcypherneo4jclient
Kiran
Kiran

Reputation: 3047

Neo4jClient datetime property - How to find all nodes between dates with Cypher

I have just started using Neo4jClient and Cypher and surprisingly I don't find any example on the net that's using the DateTime filed in the the cypher query where clause .

when I an trying to get some nodes filtered on a DateTime property, the query is not returning any results, here is an example of what I was trying:

Say I am looking for all make employees in HR deportment whose Date of birth is within a time range. the query I am trying to build is as shown below.

client.Cypher
    .Start(new { company = companyNode.Reference})
    .Match("(department)<-[:BELONGS_TO]-(employee)-[:BELONGS_TO]->(company)")
    .Where<Department>(department=>department.Name=='Human Resource')
    .AndWhere<Employee>(employee=> employee.DOB >= searchStart && employee.DOB<= searchEnd)
    .ReturnDistinct((employee)=> new {name = employee.Name});

here Employee->DOB/searchStart/searchEnd are all DateTimeOffset fields and the data stored in the graph via the neo4jclient is represented as "1990-09-28T19:02:21.7576376+05:30"

when i am debugging the code I see that Neo4jClient is actually representing the query as something like this

AND ((employee.DOB >=10/3/1988 8:16:41 PM +03:00) AND (employee.DOB <=10/3/2003 8:16:41 PM +03:00))

when I get rid of the DOB where clause i do get results.

I would really appreciate if someone can point me to how the DateTimeOffset property can be used in the queries.

Regards, Kiran

Upvotes: 0

Views: 3194

Answers (2)

ShakeyDave
ShakeyDave

Reputation: 41

There's also whole chunk of the O'Reilly book about Neo4J (that they give an electronic copy of away free from the Neo4J Site here: http://www.neo4j.org/learn) about managing dates, which I believe echoes the information in the blog linked to above. I.e. they recommend putting dates into the database as nodes (i..e. year nodes with relationships to month nodes, with relationships to day nodes) and then linking all date sensitive nodes to the relevant day.

Would get a bit painful if you want to measure things in Milliseconds, though...

Upvotes: 0

Charlotte Skardon
Charlotte Skardon

Reputation: 6270

Using the DateTimeOffset works fine for me:

private static IList<Node<DateOffsetNode>> Between(IGraphClient client, DateTimeOffset from, DateTimeOffset to)
{
    ICypherFluentQuery<Node<DateOffsetNode>> query = new CypherFluentQuery(client)
        .Start(new { n = All.Nodes })
        .Where((DateOffsetNode n) => n.Date <= to && n.Date >= from)
        .Return<Node<DateOffsetNode>>("n");

    return query.Results.ToList();
}

Where DateOffsetNode is just:

public class DateOffsetNode { public DateTimeOffset Date { get;set; } }

But another way is to store the ticks value and compare with that instead:

.Where((DateObj o) => o.Ticks < DateTime.Now.Date.Ticks)

I typically define DateObj like:

public class DateObj {
    public long Ticks { get;set; }
    public int Year { get;set; }
    public int Month { get;set;}
    public int Day { get;set;}

    public DateObj(){}
    public DateObj(DateTime dt){
        Ticks = dt.Date.Ticks;
        Year = dt.Date.Year;
        Month = dt.Date.Month;
        Day = dt.Date.Day;
    }
}

So I can also do things like:

.Where((DateObj o) => o.Year == 2013)

The equivalent for a time is to use something like the TotalMilliseconds property on the DateTime object as well:

.Where((TimeObj o) => o.TimeMs < DateTime.Now.TimeOfDay.TotalMilliseconds)

Upvotes: 1

Related Questions