CODEWITHSUNDEEP
datetimeneo4jcyphergraph-databasesneo4j-apoc
Matt Larsuma
Matt Larsuma

Reputation: 1519

Cypher query to add WHERE clause for nodes that were created within the last 24 hours

I have a query to get all of a specific type of users:

MATCH (users:User {role: "USER", hasCompletedRegistration: true})
RETURN users

And there is a createdAt datetime property. What is the cleanest way to only return users WHERE the createdAt is within the last 24 hours of when the query is executed? The goal is for a nightly CRON job to check for all newly registered users to send a welcome message, and thus the specific timeframe.

Upvotes: 1

Views: 869

Answers (2)

jose_bacoy
jose_bacoy

Reputation: 12684

You can use the function duration.inSeconds and get the hour part. Datetime() function gets the system datetime now.

MATCH (n:User )
WHERE duration.inSeconds(n.createdAt, datetime()).hours <= 24
RETURN n

Upvotes: 1

InverseFalcon
InverseFalcon

Reputation: 30397

You'll need to have the property indexed, and you'll need to leverage index-backed ordering, which will grab the ordered entries from the index instead of having to look at and filter all :User nodes.

So, for the index you first need:

CREATE INDEX ON :User(createdAt)

You should be using temporal types, so for this I'll assume a dateTime type is used.

Next for the WHERE clause, for it to leverage the index, you can't apply a function or use any accessors from the createdAt property, that needs to be left alone. We can calculate 24 hours or one day ago through using dateTime() to get the current dateTime instant, and subtraction of a duration, then we'll use an inequality to finish up.

Also, we recommend using singular instead of plural when you're not working with list types, so user instead of users, since it represents a single user per row.

MATCH (user:User {role: "USER", hasCompletedRegistration: true})
WHERE user.createdAt > dateTime() - duration({days:1})
RETURN user

If you view the EXPLAIN plan of the query, you want to see a NodeIndexSeekByRange being used. If you see any other index being used (say on the role or hasCompletedRegistration) then you can provide a planner hint to force it to use the index on createdAt:

MATCH (user:User {role: "USER", hasCompletedRegistration: true})
USING INDEX user:User(createdAt)
WHERE user.createdAt > dateTime() - duration({days:1})
RETURN user

Upvotes: 1

Related Questions