How to handle dates in neo4j

Question

I'm an historian of medieval history and I'm trying to code networks between kings, dukes, popes etc. over a period of time of about 50 years (from 1220 to 1270) in medieval Germany. As I'm not a specialist for graph-databases I'm looking for a possibility to handle dates and date-ranges.

Are there any possibilities to handle over a date-range to an edge so that the edges, which represents a relationship, disappears after e.g. 3 years?

Are there any possibility to ask for relationships who have their date-tag in a date-range?

Solution 1

The common way to deal with dates in Neo4j is storing them either as a string representation or as millis since epoch (aka msec passed since Jan 01 1970).

The first approach makes the graph more easily readable the latter allows you to do math e.g. calculate deltas.

In your case I'd store two properties called validFrom and validTo on the relationships. You queries need to make sure you're looking for the correct time interval.

E.g. to find the king(s) in charge of France from Jan 01 1220 to Dec 31st 1221 you do:

MATCH (c:Country{name:'France'})-[r:HAS_KING]->(king)
WHERE r.validFrom >= -23667123600000 and r.validTo <=-23604051600000
RETURN king, r.validFrom, r.validTo

addendum

Since Neo4j 3.0 there's the APOC library which provides couple of functions for converting timestamps to/from human readable date strings.

Solution 2

You can also store the dates in their number representation in the following format: YYYYMMDD

In your case 12200101 would be Jan 1st 1220 and 12701231 would be Dec 31st 1270.

It's a useful and readable format and you can perform range searches like:

MATCH (h:HistoricEvent)
WHERE h.date >= 12200101 AND h.date < 12701231
RETURN h

It would also let you order by dates, if you need to.

Solution 3

As of Neo4J 3.4, the system handles duration and dates, see the official documentation. See more examples here.

An example related to the original question: Retrieve the historical events that happened in the last 30 days from now :

WITH duration({days: 30}) AS duration
MATCH (h:HistoricEvent)
WHERE date() - duration < date(h.date)
RETURN h

Solution 4

Another option for dates that keeps the number of nodes/properties you create fairly low is a linked list years (earliest year of interest - latest year), one of months (1-12), and one of dates in a month (1-31). Then every "event" in your graph can be connected to a year, month, and day. This way you don't have to create a new node for every new combination of a year month and day. You just have a single set of months, one of days, and one year. I scale the numbers to make manipulating them easier like so

Years are yyyy*10000

Months are mm*100

Date are dd

so if you run a query such as

match (event)-[:happened]->(t:time) 
with event,sum(t.num) as date 
return event.name,date
order by date

You will get a list of all events in chronological order with dates like Janurary 17th, 1904 appearing as 19040117 (yyyymmdd format)

Further, since these are linked lists where, for example, ...-(t0:time {num:19040000})-[:precedes]->(t1:time {num:19050000})-... ordering is built into the nodes too.

This is, so far, how I have liked to do my event dating

View more solutions

Comments

[Andreas Kuczera]

Thanks for the quick answer. For my project it would be important to get good readability and the posibility of calcutaing deltas. At the moment I'm using iso-data like "1220-02-05". Does neo4j have a function to translate iso-Data to the millisecond-data ?

[Andreas Kuczera]

I'll take your proposal in consideration. Your example is human-readable and can be used for delta-calculation. Thanks.

[Stefan Armbruster]

Unfortunately such a conversion function does not yet exist. So you need to do it on client side.

[Andreas Kuczera]

Hello, I'm trying your date-type but neo4j comes with an error: Don't know how to compare that. Left: "12230500" (String); Right: 12290225 (Long) -- Any Idea ?

[Amin Abu-Taleb]

Yes @AndreasKuczera, your first object should be a Long as well. You should store longs and use longs as parameters for the queries

[Andreas Kuczera]

I'm just storing all values the same way: MATCH (from {id:line.id1}), (to {id:line.id2}) create from-[:NEGATIV{reltype:line.rel1, datum:line.date, datenorm:line.datenorm, vorgID:line.vorgID}]->to; Don't know why neo4j stores one number as long and the other as string. How can i force storing as long ?

[Andreas Kuczera]

The number is stored with datenorm.

[J Fabian Meier]

@AndreasKuczera If you write your dates as "1220-02-05" you can use the lexicographic order in the where clause.

[Nibroc A Rehpotsirhc]

I am trying to research the concepts of linked lists in Neo4j, after reading your post, but if you see this and could post some more details on how you implement this it would be much appreciated!

[Tshimanga]

Certainly, what would you like me to clarify?

[Nibroc A Rehpotsirhc]

I found this article... neo4j.com/blog/moving-relationships-neo4j

[Nibroc A Rehpotsirhc]

I used it as a basis to create a linked list for months, however, when following the instructions in this article, my relationships for "NEXT" were not labeled as next, but instead labeled with the ID of the relationship. Perhaps that is inconsequential. In my graph DB, I have nodes for news stories, and companies, with a MENTIONS relationship to indicate which stores mention a company. I want to also have the relationship to the date the article was written. So, how would my Year, Month, Date linked lists work together to relate to each story, that is what I can't understand yet. Thanks!

[Tshimanga]

mmm, skimmed the article and it seems it runs tangent to your desiderata unless you develop a need for relationships to be able to propagate through linked lists. Linked lists, in general, are a touch simpler in theory and in your particular use case. Firstly, the List identifier node isn't entirely necessary as node labels achieve the same effect.

[Tshimanga]

as an example for creating the month linked list, one might use the following queries: >>> UNWIND range(1,12) as number CREATE (m:Time:Month) SET m.num=number*100; which creates the nodes representing the 12 months, and to actually "link" the nodes, the following will suffice >>> MATCH (a:Month),(b:Month) WHERE b.num=a.num+100 CREATE (a)-[:succ]->(b)

[Tshimanga]

so now if you have nodes (n:Article) representing news stories, and nodes (m:Company) representing companies you might, if the article is published on 20170401, for instance, do the following to structure this subgraph: MATCH (a:Article {title:"some title"}),(c:Company {name:"some company"}) CREATE (a)-[:mentions]->(c)

[Tshimanga]

and then you would do the following to date the article:MATCH (a:Article {title:"some article"}),(y:Year {num:20170000}),(m:Month {num:400}),(d:Date {num:1}) MERGE (y)<-[:published]-(a)-[:published]->(m) merge (a)-[:published]->(d)

[Tshimanga]

now the following query MATCH (a:Article {title:"some article"})-[:published]->(t) return sum(t.num) returns sum(t.num) // 20170401

[Tshimanga]

does that answer your question(s)? let me know if there is anything I failed to adequately explain

[qualebs]

@StefanArmbruster You rock! I always get the most useful and precise information from almost all the answers you give and today I just wanted to say thanks. long live!

[Sachin Verma]

Is using @DateLong recommended?

[loopasam]

neo4j 3.4 handles dates and time, see stackoverflow.com/a/51175076/1305302 for details

[Andreas Kuczera]

Yes, 3.4 can handle dates and spatial data. A very big improvement !