CODEWITHSUNDEEP
neo4jcypher
Simon
Simon

Reputation: 537

Neo4J Cypher - subtract duration from timestamp

I am trying to create a dynamic query which will get me results from "today" (when submitted) and so many days backwards.

Lets say for the sake of the example that I want the last 14 days of data, from today.

The field first_submitted is recorded by use of timestamp()

I have tried the following code;

match(u:data) WHERE u.first_submitted > timestamp()}-duration('P14D') AND u.first_submitted < timestamp() RETURN u.first_submitted LIMIT 10

However I receive the error

Neo.ClientError.Statement.TypeError: Cannot subtract `Duration` from `Long`

I tried a few different ways of coding this but am turning up very confused with the syntax and Neo4J's documentation surrounding this.

Alternatively, if you can think of a better way to do this that isn't resource too heavy please let me know.

Thanks in advance!

P.S. I'm not sure what the "data.apoch" stuff I keep seeing is exactly but when I try to run any of these commands I receive errors so if I can avoid the need of this library please.

Upvotes: 0

Views: 1558

Answers (1)

cybersam
cybersam

Reputation: 66999

Yes, the temporal values and functions are pretty complex, and the documentation is scattered and dense.

The timestamp function returns an integer. But a Duration (which can be constructed using the duration function) can only be subtracted from a temporal instant (for example, a DateTime, which can be constructed using the datetime function).

So, for simplicity, the first_submitted property value should be something like a DateTime (which represents a date and time) instead of an integer. For example:

CREATE (:data {id: 123, first_submitted: datetime()})

Assuming that is true, your query could look like this:

WITH datetime()-duration('P14D') AS threshold
MATCH (u:data)
WHERE u.first_submitted > threshold
RETURN u.first_submitted LIMIT 10

Note: it is not necessary to test that the stored DateTime is before the current DateTime, since presumably all first_submitted values are in the past.

Also, you may want to create an index on :data(first_submitted) to improve performance.

Upvotes: 1

Related Questions