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

Question

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

Where are you getting the query representation from? QueryParameters or DebugQueryText? — Tatham Oddie
@TathamOddie : Thanks, I didn't notice these properties until now, so used the QuickWatch window of IDE to get the query from there. I now see that the parameters are represented correctly in that QueryParameters and QueryText, however the results are not returned. May be the data in the database is not stored correctly as I see the value in the admin window as a string. how do I know the actual data type of the property itself, may be its going wrong there? — Kiran
BTW Just came across a post blog.nigelsmall.com/2012/09/modelling-dates-in-neo4j.html , looks DateTime is not a supported in neo4j, which is really strange as I thought its very common to filter things by time window. How are others handling this? — Kiran

Charlotte Skardon Charlotte Skardon · Accepted Answer · 2013-09-25T06:52:26

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)

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

2 Answers