# Count and nodes for each day of week and each hour

**URL:** <https://community.neo4j.com/t/count-and-nodes-for-each-day-of-week-and-each-hour/35341>\
**Category:** Cypher\
**Created:** [March 16, 2021, 10:43am UTC](https://community.neo4j.com/t/count-and-nodes-for-each-day-of-week-and-each-hour/35341 "2021-03-16T10:43:22Z")\
**Posts on this page:** 1\
**Showing post:** 4

<div class="post-metadata">

**Author:** ![chrszrkl](https://sea1.discourse-cdn.com/flex021/user_avatar/community.neo4j.com/chrszrkl/32/14700_2.png) [@chrszrkl](https://community.neo4j.com/u/chrszrkl)\
**Post date:** [March 21, 2021, 9:43am UTC](https://community.neo4j.com/t/count-and-nodes-for-each-day-of-week-and-each-hour/35341/4 "2021-03-21T09:43:04Z")

</div>

I further improved my Cypher query with a variable StartDate and calculated EndDate, which will be calculated by adding X days/weeks/months etc. onto the StartDate. Moreover, instead of providing a static list of WeekDays like in my example above, I now generate a list of days starting from the StartDate and iteratively add 0, 1, 2, ..., 6 days. So my week does not necessarily start at Monday, but at the given StartDate and ends 6 days later (the EndDate is irrelevant here).

I found this post as a very helpful: [Creating date nodes by looping through a start date and an end date - #3 by tony.chiboucas](https://community.neo4j.com/t/creating-date-nodes-by-looping-through-a-start-date-and-an-end-date/26205/3)

```auto
WITH datetime("2021-03-01") AS StartDate
WITH StartDate, StartDate + duration({days: 7}) AS EndDate // Add 7 days because we use < symbol in the WHERE clause
MATCH (o:Order)
WHERE datetime(o.createdAt) >= StartDate AND datetime(o.createdAt) < EndDate //IMPORTANT: use less than (<) EndDate 
WITH StartDate, EndDate, [i IN range(0, 6) | StartDate+duration({days: i})] as WeekDays, collect(datetime(o.createdAt)) as OrderTimestamps
UNWIND WeekDays AS WeekDay
RETURN 
	apoc.date.format(WeekDay.epochMillis, 'ms', 'E') as WeekDay,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 0]) as h00,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 1]) as h01,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 2]) as h02,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 3]) as h03,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 4]) as h04,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 5]) as h05,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 6]) as h06,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 7]) as h07,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 8]) as h08,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 9]) as h09,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 10]) as h10,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 11]) as h11,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 12]) as h12,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 13]) as h13,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 14]) as h14,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 15]) as h15,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 16]) as h16,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 17]) as h17,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 18]) as h18,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 19]) as h19,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 20]) as h20,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 21]) as h21,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 22]) as h22,
	size([ts IN OrderTimestamps WHERE ts.dayOfWeek = WeekDay.dayOfWeek and ts.hour = 23]) as h23

```

---

_[View the full topic](https://community.neo4j.com/t/count-and-nodes-for-each-day-of-week-and-each-hour/35341)._