Neo4j date and datetime comparison issues

Question

I've got a very strange behavior of neo4j engine. Let's assume we have node with date and datetime type properties:

merge (n:Item {date: date("2019-10-21"), datetime: datetime("2019-10-21") }  )
return n

These queries works fine:

match (n:Item)
where n.date>=date("2019-10-20")
return n

match (n:Item)
where n.datetime>=datetime("2019-10-20")
return n

When I try to compare date with datetime (or datetime with date) the empty dataset returns:

match (n:Item)
where n.date>=datetime("2019-10-20")
return n

match (n:Item)
where n.datetime>=date("2019-10-20")
return n

Neo4j version is 3.5.11.

user_974731 · Answer

In neo4j, you cannot directly compare temporal values of different types (e.g., date and datetime).

With your use case, you could convert the date to a datetime before doing the comparison.

For example, your last 2 queries could convert the date to a datetime:

MATCH (n:Item)
WHERE datetime({date:n.date}) >= datetime("2019-10-20")
RETURN n;


MATCH (n:Item)
WHERE n.datetime >= datetime({date:date("2019-10-20")})
RETURN n;

But with your use case it would also be valid (and perhaps more efficient) to convert the datetime to a date (notice that is is OK to pass a datetime as the date property value):
```
MATCH (n:Item)
WHERE n.date >= date({date: datetime("2019-10-20")})
RETURN n;


MATCH (n:Item)
WHERE date({date: n.datetime}) >= date("2019-10-20")
RETURN n;
```

Yet another approach is to compare the year and ordinalDay values, as in:

WITH datetime("2019-10-20") AS dt
MATCH (n:Item)
WHERE n.date.year >= dt.year AND n.date.ordinalDay >= dt.ordinalDay
RETURN n;

WITH date("2019-10-20") AS d
MATCH (n:Item)
WHERE n.datetime.year >= d.year AND n.datetime.ordinalDay >= d.ordinalDay
RETURN n;

Neo4j date and datetime comparison issues

1 Answers