Getting the node counts between 10 minutes periods

[taylangezici1]

I have a node called (:Post) with a property called 'CreatedAt'.

The CreatedAt propery has a time value such as "2021-01-01T08:00:00Z".

What I want to do is to get the node counts between 10 minutes periods in all database.

I was able to find the solution for the days with the following query (even though it was super slow):

WITH date('2020-01-01') AS startDate, date('2020-02-01') AS endDate
WITH startDate, duration.inDays(startDate, endDate).days AS days
WITH [day in range(0, days) | startDate + duration({days: day})] AS dates
UNWIND dates AS date
MATCH (m:Post)
WHERE date(datetime(m.CreatedDate)) = date
RETURN date, COUNT(*) AS count

This query returns an output like this:

date count
"2021-09-10" 690

"2021-09-11" 701

I need an output like the following:

dateStart dateEnd count
"2021-09-10T08:00:00" "2021-09-10T08:10:00" 50

"2021-09-10T08:10:00" "2021-09-10T08:20:00" 40

"2021-09-10T08:20:00" "2021-09-10T08:30:00" 60
...

I would be appreciated for any kind of help as I was not able to find a solution for such challenge.

[giuseppe_villan · Accepted solution]

Maybe it should work in this way:

WITH datetime('2021-01-01') as startDate,  datetime('2021-01-02')  as endDate, 10 * 60 * 1000 as msToMins
WITH range(startDate.epochMillis, endDate.epochMillis, msToMins) as range, msToMins
UNWIND range as single // create a range of epoch millis with a step of 10 mins
with single, msToMins
MATCH (n:Post) 
WHERE datetime(n.createdAt).epochMillis >= single AND datetime(n.createdAt).epochMillis < single + msToMins // comparing epochs
RETURN datetime({epochMillis: single}), datetime({epochMillis: single + msToMins}), COUNT(n)

Note that if CreatedAt property is a datetime and not a string, you could remove datetime(..) in WHERE clause (that is,WHERE n.createdAt.epochMillis >= single AND n.createdAt.epochMillis < single + msToMins).

You could change MATCH to OPTIONAL MATCH to retrieve all ranges, even if count is 0.