Skip to content

Instantly share code, notes, and snippets.

@kbastani
Last active July 14, 2020 20:36
Show Gist options
  • Save kbastani/8519557 to your computer and use it in GitHub Desktop.
Save kbastani/8519557 to your computer and use it in GitHub Desktop.
This gist is a Neo4j Cypher query for merging a calendar graph for a specific year. This query has 4 levels of indexes, consisting of year, month, day, hour.
// Enter the day you would like to create
WITH { day: 18, month: 1, year: 2014 } as dayMap
// Merge hours in a day
MERGE (thisDay:Day { day: dayMap.day, month: dayMap.month, year: dayMap.year })
MERGE (firstHour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 1 })
CREATE (thisDay)-[:FIRST]->(firstHour)
FOREACH (i IN tail(range(1, 24)) |
MERGE (thishour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: i })
MERGE (lasthour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: i - 1 })
CREATE (lasthour)-[:NEXT]->(thishour))
MERGE (lastHour:Hour { day: dayMap.day, month: dayMap.month, year: dayMap.year, hour: 24 })
CREATE (thisDay)-[:LAST]->(lastHour)
// Enter the calendar year you would like to create
MERGE (thisYear:Year { year: 2014 })
// This date map enforces the rules for days in a month (leap year not yet implemented)
WITH {
january: range(1, 31),
february: range(1, 28),
march: range(1, 31),
april: range(1, 30),
may: range(1, 31),
june: range(1, 30),
july: range(1, 31),
august: range(1, 31),
september: range(1, 30),
october: range(1, 31),
november: range(1, 30),
december: range(1, 31)
} AS dateMap, thisYear
// Merge 12 months of the year using the date map as the calendar rules
MERGE (january:Month { month: 1, year: thisYear.year })
CREATE (thisYear)-[:HAS_MONTH]->(january)
MERGE (firstDay:Day { day: 1, month: january.month, year: thisYear.year })
CREATE (january)-[:FIRST]->(firstDay)
FOREACH (i IN tail(dateMap.january) |
MERGE (thisday:Day { day: i, month: january.month, year: thisYear.year })
MERGE (lastday:Day { day: i - 1, month: january.month, year: thisYear.year })
CREATE (lastday)-[:NEXT]->(thisday))
MERGE (lastDay:Day { day: last(dateMap.january), month: january.month, year: thisYear.year })
CREATE (january)-[:LAST]->(lastDay)
WITH dateMap, thisYear
MERGE (february:Month { month: 2, year: thisYear.year })
CREATE (thisYear)-[:HAS_MONTH]->(february)
MERGE (firstDay:Day { day: 1, month: february.month, year: thisYear.year })
CREATE (february)-[:FIRST]->(firstDay)
FOREACH (i IN tail(dateMap.february) |
MERGE (thisday:Day { day: i, month: february.month, year: thisYear.year })
MERGE (lastday:Day { day: i - 1, month: february.month, year: thisYear.year })
CREATE (lastday)-[:NEXT]->(thisday))
MERGE (lastDay:Day { day: last(dateMap.february), month: february.month, year: thisYear.year })
CREATE (february)-[:LAST]->(lastDay)
WITH dateMap, thisYear
MERGE (march:Month { month: 3, year: thisYear.year })
CREATE (thisYear)-[:HAS_MONTH]->(march)
MERGE (firstDay:Day { day: 1, month: march.month, year: thisYear.year })
CREATE (march)-[:FIRST]->(firstDay)
FOREACH (i IN tail(dateMap.march) |
MERGE (thisday:Day { day: i, month: march.month, year: thisYear.year })
MERGE (lastday:Day { day: i - 1, month: march.month, year: thisYear.year })
CREATE (lastday)-[:NEXT]->(thisday))
MERGE (lastDay:Day { day: last(dateMap.march), month: march.month, year: thisYear.year })
CREATE (march)-[:LAST]->(lastDay)
WITH dateMap, thisYear
MERGE (april:Month { month: 4, year: thisYear.year })
CREATE (thisYear)-[:HAS_MONTH]->(april)
MERGE (firstDay:Day { day: 1, month: april.month, year: thisYear.year })
CREATE (april)-[:FIRST]->(firstDay)
FOREACH (i IN tail(dateMap.april) |
MERGE (thisday:Day { day: i, month: april.month, year: thisYear.year })
MERGE (lastday:Day { day: i - 1, month: april.month, year: thisYear.year })
CREATE (lastday)-[:NEXT]->(thisday))
MERGE (lastDay:Day { day: last(dateMap.april), month: april.month, year: thisYear.year })
CREATE (april)-[:LAST]->(lastDay)
WITH dateMap, thisYear
MERGE (may:Month { month: 5, year: thisYear.year })
CREATE (thisYear)-[:HAS_MONTH]->(may)
MERGE (firstDay:Day { day: 1, month: may.month, year: thisYear.year })
CREATE (may)-[:FIRST]->(firstDay)
FOREACH (i IN tail(dateMap.may) |
MERGE (thisday:Day { day: i, month: may.month, year: thisYear.year })
MERGE (lastday:Day { day: i - 1, month: may.month, year: thisYear.year })
CREATE (lastday)-[:NEXT]->(thisday))
MERGE (lastDay:Day { day: last(dateMap.may), month: may.month, year: thisYear.year })
CREATE (may)-[:LAST]->(lastDay)
WITH dateMap, thisYear
MERGE (june:Month { month: 6, year: thisYear.year })
CREATE (thisYear)-[:HAS_MONTH]->(june)
MERGE (firstDay:Day { day: 1, month: june.month, year: thisYear.year })
CREATE (june)-[:FIRST]->(firstDay)
FOREACH (i IN tail(dateMap.june) |
MERGE (thisday:Day { day: i, month: june.month, year: thisYear.year })
MERGE (lastday:Day { day: i - 1, month: june.month, year: thisYear.year })
CREATE (lastday)-[:NEXT]->(thisday))
MERGE (lastDay:Day { day: last(dateMap.june), month: june.month, year: thisYear.year })
CREATE (june)-[:LAST]->(lastDay)
WITH dateMap, thisYear
MERGE (july:Month { month: 7, year: thisYear.year })
CREATE (thisYear)-[:HAS_MONTH]->(july)
MERGE (firstDay:Day { day: 1, month: july.month, year: thisYear.year })
CREATE (july)-[:FIRST]->(firstDay)
FOREACH (i IN tail(dateMap.july) |
MERGE (thisday:Day { day: i, month: july.month, year: thisYear.year })
MERGE (lastday:Day { day: i - 1, month: july.month, year: thisYear.year })
CREATE (lastday)-[:NEXT]->(thisday))
MERGE (lastDay:Day { day: last(dateMap.july), month: july.month, year: thisYear.year })
CREATE (july)-[:LAST]->(lastDay)
WITH dateMap, thisYear
MERGE (august:Month { month: 8, year: thisYear.year })
CREATE (thisYear)-[:HAS_MONTH]->(august)
MERGE (firstDay:Day { day: 1, month: august.month, year: thisYear.year })
CREATE (august)-[:FIRST]->(firstDay)
FOREACH (i IN tail(dateMap.august) |
MERGE (thisday:Day { day: i, month: august.month, year: thisYear.year })
MERGE (lastday:Day { day: i - 1, month: august.month, year: thisYear.year })
CREATE (lastday)-[:NEXT]->(thisday))
MERGE (lastDay:Day { day: last(dateMap.august), month: august.month, year: thisYear.year })
CREATE (august)-[:LAST]->(lastDay)
WITH dateMap, thisYear
MERGE (september:Month { month: 9, year: thisYear.year })
CREATE (thisYear)-[:HAS_MONTH]->(september)
MERGE (firstDay:Day { day: 1, month: september.month, year: thisYear.year })
CREATE (september)-[:FIRST]->(firstDay)
FOREACH (i IN tail(dateMap.september) |
MERGE (thisday:Day { day: i, month: september.month, year: thisYear.year })
MERGE (lastday:Day { day: i - 1, month: september.month, year: thisYear.year })
CREATE (lastday)-[:NEXT]->(thisday))
MERGE (lastDay:Day { day: last(dateMap.september), month: september.month, year: thisYear.year })
CREATE (september)-[:LAST]->(lastDay)
WITH dateMap, thisYear
MERGE (october:Month { month: 10, year: thisYear.year })
CREATE (thisYear)-[:HAS_MONTH]->(october)
MERGE (firstDay:Day { day: 1, month: october.month, year: thisYear.year })
CREATE (october)-[:FIRST]->(firstDay)
FOREACH (i IN tail(dateMap.october) |
MERGE (thisday:Day { day: i, month: october.month, year: thisYear.year })
MERGE (lastday:Day { day: i - 1, month: october.month, year: thisYear.year })
CREATE (lastday)-[:NEXT]->(thisday))
MERGE (lastDay:Day { day: last(dateMap.october), month: october.month, year: thisYear.year })
CREATE (october)-[:LAST]->(lastDay)
WITH dateMap, thisYear
MERGE (november:Month { month: 11, year: thisYear.year })
CREATE (thisYear)-[:HAS_MONTH]->(november)
MERGE (firstDay:Day { day: 1, month: november.month, year: thisYear.year })
CREATE (november)-[:FIRST]->(firstDay)
FOREACH (i IN tail(dateMap.november) |
MERGE (thisday:Day { day: i, month: november.month, year: thisYear.year })
MERGE (lastday:Day { day: i - 1, month: november.month, year: thisYear.year })
CREATE (lastday)-[:NEXT]->(thisday))
MERGE (lastDay:Day { day: last(dateMap.november), month: november.month, year: thisYear.year })
CREATE (november)-[:LAST]->(lastDay)
WITH dateMap, thisYear
MERGE (december:Month { month: 12, year: thisYear.year })
CREATE (thisYear)-[:HAS_MONTH]->(december)
MERGE (firstDay:Day { day: 1, month: december.month, year: thisYear.year })
CREATE (december)-[:FIRST]->(firstDay)
FOREACH (i IN tail(dateMap.december) |
MERGE (thisday:Day { day: i, month: december.month, year: thisYear.year })
MERGE (lastday:Day { day: i - 1, month: december.month, year: thisYear.year })
CREATE (lastday)-[:NEXT]->(thisday))
MERGE (lastDay:Day { day: last(dateMap.december), month: december.month, year: thisYear.year })
CREATE (december)-[:LAST]->(lastDay)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment