18

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?

Andreas Kuczera
  • 353
  • 1
  • 2
  • 14

4 Answers4

13

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.

Stefan Armbruster
  • 39,465
  • 6
  • 87
  • 97
  • 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 Mar 30 '15 at 11:53
  • Unfortunately such a conversion function does not yet exist. So you need to do it on client side. – Stefan Armbruster Mar 30 '15 at 13:21
  • @AndreasKuczera If you write your dates as "1220-02-05" you can use the lexicographic order in the where clause. – J Fabian Meier Aug 31 '16 at 13:31
  • 1
    @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! – qualebs Aug 10 '17 at 09:27
  • Is using @DateLong recommended? – Sachin Verma Apr 09 '18 at 11:54
  • neo4j 3.4 handles dates and time, see https://stackoverflow.com/a/51175076/1305302 for details – loopasam Jul 04 '18 at 13:40
7

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.

devtty1er
  • 48
  • 4
Amin Abu-Taleb
  • 4,423
  • 6
  • 33
  • 50
  • 3
    I'll take your proposal in consideration. Your example is human-readable and can be used for delta-calculation. Thanks. – Andreas Kuczera Mar 30 '15 at 12:02
  • 1
    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 ? – Andreas Kuczera Mar 31 '15 at 15:15
  • Yes @AndreasKuczera, your first object should be a Long as well. You should store longs and use longs as parameters for the queries – Amin Abu-Taleb Mar 31 '15 at 15:21
  • 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 Mar 31 '15 at 16:08
  • The number is stored with datenorm. – Andreas Kuczera Mar 31 '15 at 16:09
7

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
loopasam
  • 3,027
  • 2
  • 21
  • 22
1

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

Tshimanga
  • 845
  • 6
  • 16
  • 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! – Nibroc A Rehpotsirhc Jun 23 '17 at 01:05
  • Certainly, what would you like me to clarify? – Tshimanga Jun 24 '17 at 13:51
  • I found this article... https://neo4j.com/blog/moving-relationships-neo4j/ – Nibroc A Rehpotsirhc Jun 24 '17 at 15:56
  • 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! – Nibroc A Rehpotsirhc Jun 24 '17 at 15:59
  • 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 Jun 24 '17 at 20:28
  • 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 Jun 24 '17 at 20:31
  • 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 Jun 24 '17 at 20:39
  • 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 Jun 24 '17 at 20:51
  • now the following query `MATCH (a:Article {title:"some article"})-[:published]->(t) return sum(t.num)` returns `sum(t.num) // 20170401` – Tshimanga Jun 24 '17 at 20:55
  • does that answer your question(s)? let me know if there is anything I failed to adequately explain – Tshimanga Jun 24 '17 at 20:57