Dates, datetimes, and durations

[adam_cowley]

What if we want to filter our articles based on these temporal values.

Let’s start by finding the articles that were published on 1st June 2019. The following query does this:

MATCH (article:Article)
WHERE article.datePublished = date({year: 2019, month: 6, day: 1})
RETURN article.title AS title,
       article.created AS created,
       article.datePublished AS datePublished,
       article.readingTime AS readingTime

Table 3. Results title created datePublished readingTime "Cypher Basics I" 2019-06-01T18:40:32.142+01:00 2019-06-01 P0M0DT135S

What about if we want to find all the articles published in June 2019? We might write the following query to do this:

MATCH (article:Article)
WHERE article.datePublished = date({year: 2019, month: 6})
RETURN article.title AS title,
       article.created AS created,
       article.datePublished AS datePublished,
       article.readingTime AS readingTime

If we run this query we’ll get the following results:

Table 4. Results title created datePublished readingTime "Cypher Basics I" 2019-06-01T18:40:32.142+01:00 2019-06-01 P0M0DT135S

This doesn’t seem right - what about the Cypher Basics II article that was published on 2nd June 2019? The problem we have here is that date({year: 2019, month:6}) returns 2019-06-01, so we’re only finding articles published on 1st June 2019.

We need to tweak our query to find articles published between June 1st 2019 and July 1st 2019. The following query does this:

MATCH (article:Article)
WHERE date({year: 2019, month: 7}) > article.datePublished >= date({year: 2019, month: 6})
RETURN article.title AS title,
       article.created AS created,
       article.datePublished AS datePublished,
       article.readingTime AS readingTime

Table 5. Results title created datePublished readingTime "Cypher Basics I" 2019-06-01T18:40:32.142+01:00 2019-06-01 P0M0DT135S "Cypher Basics II" 2019-06-02T10:23:32.122+01:00 2019-06-02 P0M0DT150S

What about if we want to filter based on the created property, which stores Datetime values? We need to take the same approach when filtering Datetime values as we did with Date values. The following query finds the articles created after July 2019:

MATCH (article:Article)
WHERE article.created > datetime({year: 2019, month: 7})
RETURN article.title AS title,
       article.created AS created,
       article.datePublished AS datePublished,
       article.readingTime AS readingTime

Table 6. Results title created datePublished readingTime "Dates, Datetimes, and Durations in Neo4j" 2019-09-25T06:04:39.072Z 2019-09-25 P0M0DT210S

And finally filtering durations. We might be interested in finding articles that can be read in 3 minutes or less.

We’ll start with the following query:

MATCH (article:Article)
WHERE article.readingTime <= duration("PT3M")
RETURN article.title AS title,
       article.created AS created,
       article.datePublished AS datePublished,
       article.readingTime AS readingTime

If we execute that query we’ll see the following output:

Neo.ClientError.Statement.SyntaxError: Type mismatch: expected Float, Integer, Point, String, Date, Time, LocalTime, LocalDateTime or DateTime but was Duration (line 2, column 29 (offset: 52))
"WHERE article.readingTime < duration("PT3M")"
                             ^

If we want to compare durations we need to do that comparison by adding those durations to dates. We don’t really care about dates for our query so we’ll just use the current time to work around this issue. We can get the current time by calling the datetime() function.

Our updated query reads like this:

MATCH (article:Article)
WHERE datetime() + article.readingTime <= datetime() + duration("PT3M")
RETURN article.title AS title,
       article.created AS created,
       article.datePublished AS datePublished,
       article.readingTime AS readingTime

Table 7. Results title created datePublished readingTime "Cypher Basics I" "01 June 2019 18:40" "01 June 2019" "02:15" "Cypher Basics II" "02 June 2019 10:23" "02 June 2019" "02:30"

---

This is a companion discussion topic for the original entry at https://neo4j.com/developer/cypher/dates-datetimes-durations/