DateTime comparison in WHERE condition

[TomaszG]

Hello, I'm trying to use Neo4jClient to implement a few basic queries and some obstacles came along when I tried to query Neo4j datetime properties with .NET datetime objects. Let's assume I have the following model:

    public class Something
    {
        [Neo4jDateTime]
        [JsonProperty("arrival_date_time")]
        public DateTime Arrival { get; set; }

        [Neo4jDateTime]
        [JsonProperty("departure_date_time")]
        public DateTime Departure { get; set; }
    }

...and the following query:

DateTime arrival = DateTime.Parse("2019-03-22T10:00:00");
var query = client.Cypher
    .Match($"(thing:{typeof(Something).Name})")
    .Where((Something thing) => thing.Arrival > arrival)
    .Return<Something>("thing");

This produces the following Neo4j query:

MATCH (thing:Something)
WHERE (thing.arrival_date_time > "2019-03-22T10:00:00")
RETURN thing

...which seems to be incorrect as in order to get the correct result, the query parameter should be parsed to Neo4j DateTime object, as below:

MATCH (thing:Something)
WHERE (thing.arrival_date_time > datetime("2019-03-22T10:00:00"))
RETURN thing

Am I doing something wrong or is it a bug?

[cskardon]

Hmmm.. That would be a bug

[RustyF]

I think I've hit the same problem as all my initial graphs use dates considerably. I've narrowed it down to this sample, which shows that the WHERE clause never returns true, even though it should always.

public class TypesResult
{
  public DateTime DateInCypher {get;set;}
  public DateTime DateInCsharp {get;set;}
}

var dateInCSharp = DateTime.Now;

var result = client.Cypher
  .With("datetime() as dateincypher, {dateincsharp} as dateincsharp")
  .Where("dateincypher > dateincsharp OR dateincsharp <= dateincypher")
  .WithParam("dateincsharp", dateInCSharp)
  .Return<TypesResult>((dateincypher,dateincsharp) => new TypesResult {
    DateInCsharp = dateincsharp.As<DateTime>(),
    DateInCypher = dateincypher.As<DateTime>(),
  })
  .Results; // Produces []

It works if I wrap the C# datetime parameter in an explicit datetime():-

.With("datetime() as dateincypher, datetime({dateincsharp}) as dateincsharp")

[RustyF]

Happy to try and fix this with some pointers.

[Clooney24]

I don't think this is a bug it's just not yet supported. C# DateTime is serialized to a Neo4j string property in the format "2020-03-09T16:45:00.9199162+01:00" and back from string to C# DateTime. The neo4j temporal types and features which where introduced end of 2018 are not yet supported, see Issue #291

[cskardon]

Hmm not entirely true - adding the [Neo4jDateTime] attribute to a property which is a DateTime will serialize it as a Neo4j DateTime.

The problem is how the Where knows this. Solution wise - I'm not sure how you would fix it - my gut feeling is that you either have a global setting which then treats all DateTime instances as one or the other. When you pass in a parameter it doesn't know there is an attribute.

I guess maybe an overload to AddParam which let's you say it should be a Neo4j datetime would be the quickest route - does force you into a .WithParam scenario though