I'm trying to convert a date into a YYYYMMDD and YYYYMM format in order to create an index for a GraphAware TimeTree, as suggested in this post.
As noted in their blog, the authors originally used as MM/DD/YYYY format for the UUID, which leads to some issues with sorting.
I've tried converting the original code as follows:
MATCH (root:TimeTreeRoot)-[:CHILD]->(year:Year)
WITH collect(year) AS years UNWIND years AS y
SET y.uuid = toString(y.value)
WITH y
MATCH (y)-[:CHILD]->(month:Month)
WITH y, collect(month) AS months UNWIND months AS m
SET m.uuid = y.value + substring("00", size(m.value)) + m.value
WITH y, m
MATCH (m)-[:CHILD]->(day:Day)
WITH y, m, collect(day) AS days UNWIND days AS d
SET d.uuid = y.value + substring("00", size(m.value)) + m.value + substring("00", size(d.value)) + d.value
;
First of all this does not seem very efficient to me. Second, I'm not getting the result I would expect. The days and months less than 10 are left padded with a0, but so are the days and months greater than 9. Running
MATCH (n:Month) WHERE EXISTS(n.uuid) RETURN DISTINCT "node" as entity, n.uuid AS uuid LIMIT 25 UNION ALL MATCH ()-[r]-() WHERE EXISTS(r.uuid) RETURN DISTINCT "relationship" AS entity, r.uuid AS uuid LIMIT 25
returns
|"node"|"201901"|
|"node"|"197001"|
|"node"|"201902"|
|"node"|"201903"|
|"node"|"201904"|
|"node"|"201905"|
|"node"|"201906"|
|"node"|"201907"|
|"node"|"201908"|
|"node"|"201909"|
|"node"|"2019010"|
|"node"|"2019011"|
|"node"|"2019012"|
Running the same query of Day returns
|node"|"201901027"|
|"node"|"201901028"|
|"node"|"201901029"|
|"node"|"201901030"|
|"node"|"201901031"|
|"node"|"20190201"|
|"node"|"20190202"|
|"node"|"20190203"|
|"node"|"20190204"|
|"node"|"20190205"|
|"node"|"20190206"|
|"node"|"20190207"|
|"node"|"20190208"|
|"node"|"20190209"|
|"node"|"201902010"|
|"node"|"201902011"|
|"node"|"201902012"|
|"node"|"201902013"|
|"node"|"201902014"|
|"node"|"201902015"|
|"node"|"201902016"|
|"node"|"201902017"|
I was looking for a format statement along the lines of the code below, but I cannot find anything in the Neo4j manuals.
SET m.uuid = y.value + format("00", m.value)
#GraphAware #TimeTree #format #convert-time