in neo4j cypher ~ read.

A Date Hierarchy for Neo4j

I wrote this a while ago based on this excellent post and added a few more attributes. Given that Neo4j doesn't have a datatype to deal with dates it might come in handy for you too.

It will generate a calendar between the years specified at the top of the script (1970 to 2050) and create Day vertexes with attributes of year, month, day, dayName (day of week) and workDay (binary). It will then generate create NEXT directed edges between each Day, Month and Year object and create the HAS_MONTH and HAS_DAY edges to join Year to Month to Day so you can traverse the hierarchy quickly.

WITH  
   range (1970, 2050) AS years
  ,range (1,12) AS months
  ,['January','February','March','April','May','June','July','August','September','October','November','December' ] AS monthName
  ,['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'] as dayName
  ,[0,1,1,1,1,1,0] as workDay
  ,[11,12,1,2,3,4,5,6,7,8,9,10] AS monthTable
FOREACH (year IN years |  
  MERGE (y:Year {year: year})
  FOREACH (month IN months |
    CREATE (m:Month {year: year, month: month, Name: monthName[month-1] + ' ' + year})
    MERGE (y)–[:HAS_MONTH]->(m)
    FOREACH (day IN (CASE
      WHEN month IN [1,3,5,7,8,10,12] THEN range (1,31)
      WHEN month = 2 THEN
        CASE
          WHEN year % 4 <> 0 THEN range (1, 28)
          WHEN year % 100 <> 0 THEN range (1, 29)
          WHEN year % 400 <> 0 THEN range (1, 29)
          ELSE range (1,28)
        END
      ELSE range (1,30)
    END) |
      CREATE (d:Day {
         year: toInt(year)
        ,month: toInt(month)
        ,day: toInt(day)
        ,Name: day+' '+monthName[month-1]+' '+ year
        ,dateKey: toInt(
          toString(year)
          + CASE WHEN length(toString(month)) = 1 THEN '0'+toString(month) ELSE toString(month) END
          + CASE WHEN length(toString(day)) = 1 THEN '0'+toString(day) ELSE toString(day) END
        )
        ,dayName: dayName[toInt(
              (
              toFloat(day)
              +floor(2.6 * toFloat(monthTable[month-1]) - 0.2)
              -toFloat(2*toInt(left(toString(year),2)))
              +toFloat(right(toString(CASE WHEN month IN [1,2] THEN year-1 ELSE year END),2))
              +floor(toFloat(right(toString(CASE WHEN month IN [1,2] THEN year-1 ELSE year END),2))/4)
              +floor(toFloat(left(toString(year),2))/4)
              ) % 7
            )]

        ,workDay: toInt(workDay[toInt(
              (
              toFloat(day)
              +floor(2.6 * toFloat(monthTable[month-1]) - 0.2)
              -toFloat(2*toInt(left(toString(year),2)))
              +toFloat(right(toString(CASE WHEN month IN [1,2] THEN year-1 ELSE year END),2))
              +floor(toFloat(right(toString(CASE WHEN month IN [1,2] THEN year-1 ELSE year END),2))/4)
              +floor(toFloat(left(toString(year),2))/4)
              ) % 7
            )])
      })
      MERGE (m)–[:HAS_DAY]->(d)
    )
  )
)


WITH *

MATCH (year:Year)–[:HAS_MONTH]->(month)–[:HAS_DAY]->(day)  
WITH DISTINCT year, month, day  
ORDER BY year.year, month.month, day.day  
WITH collect(day) as days  
FOREACH (i in RANGE(0, length(days)-2) |  
  FOREACH (day1 in [days[i]] |
    FOREACH (day2 in [days[i+1]] |
      CREATE UNIQUE (day1)–[:NEXT]->(day2)
    )
  )
)

WITH *

MATCH (year:Year)–[:HAS_MONTH]->(month)  
WITH DISTINCT year, month  
ORDER BY year.year, month.month  
WITH collect(month) as months  
FOREACH (i in RANGE(0, length(months)-2) |  
  FOREACH (month1 in [months[i]] |
    FOREACH (month2 in [months[i+1]] |
      CREATE UNIQUE (month1)–[:NEXT]->(month2)
    )
  )
)

WITH *

MATCH (year:Year)  
WITH DISTINCT year  
ORDER BY year.year  
WITH collect(year) as years  
FOREACH (i in RANGE(0, length(years)-2) |  
  FOREACH (year1 in [years[i]] |
    FOREACH (year2 in [years[i+1]] |
      CREATE UNIQUE (year1)–[:NEXT]->(year2)
    )
  )
)
comments powered by Disqus