CODEWITHSUNDEEP
neo4j
Dominus
Dominus

Reputation: 998

Datetime comparison query doesn't return any results

I'm trying to get a simple date-time comparison to work, but the query doesn't return any results.

The query is

MATCH (n:Event) WHERE n.start_datetime > datetime("2019-06-01T18:40:32.142+0100")  RETURN n.start_datetime

According to this documentation page, this type of comparisons should work. I've also tried creating the datetime object explicitly, for instance with datetime({year: 2019, month: 7}).

I've checked that the start_datetime is in fact well formatted, by checking if the values start_datetime.year for example was correct, and couldn't find any error. Given that all the records in the database are from 2021, the above query should return every event, yet is returning nothing.

Doing the query using only the year comparison instead of doing full datetime comparison works:

MATCH (n:Event) WHERE n.start_datetime.year > datetime("2019-06-01T18:40:32.142+0100").year  RETURN n.start_datetime

Upvotes: 1

Views: 536

Answers (2)

Dominus
Dominus

Reputation: 998

It turns out the error was due to the timezone. Neo4j had saved the properties as LocalDateTime, which apparently can't be compared to ZonedDateTime.

I used py2neo for most of the nodes management, and the solution was to give a specific timezone to the python property. This was done (in my case) using:

datetime.datetime.fromtimestamp(kwargs["end"], pytz.UTC)

After that, I was able to do the comparisons.

Hopes this saves a couple of hours to future developers.

Upvotes: 0

jose_bacoy
jose_bacoy

Reputation: 12704

Double check the data type of start_datetime. It can be either in epoch seconds or epoch milliseconds. You need to convert the epoch format to datetime, so that both are on the same data type. The reason that your 2nd query works (.year) is because .year returns an integer value.

Run below to get samples:

 MATCH (n:Event) 
 RETURN distinct n.start_datetime LIMIT 5

Then if you see that it is 10 digits then it is in epochSeconds. If yes, then run below query:

 MATCH (n:Event) 
 WHERE n.start_datetime is not null 
 AND datetime({epochSeconds: n.start_datetime}) > datetime("2019-06-01T18:40:32.142+0100") 
 RETURN n.start_datetime 
 LIMIT 25

Upvotes: 1

Related Questions