In neo4j, how can I index by date and search in a date range?

Question

In neo4j, how can I index by date and search in a date range. Also for times, I would like to search between 8am and 9am in a date range as well.

How is cipher going to help me index? – Phil Feb 26 '12 at 08:49 — Phil, Feb 26 '12 at 08:49

score 24 · Accepted Answer · answered Feb 05 '12 at 06:28

24

Index the dates and times as integer timestamps. Then you can easily search in an index for dates between other timestamps. You can also index the time part of the timestamp separately as another integer, allowing you to query for specific times between given dates.

Example: The date and time to store is "2012-02-05 8:15 AM" So in your index, store "timestamp=1328447700" and "time=815"

Now you want to query the index for all events between 2012-02-01 and 2012-02-10 that occurred from 8:00 am to 9:00 am. You do that by querying the index for "timestamp>=1328072400 and timestamp<=1328936399 and time>=800 and time<=900"

The exact syntax for doing this depends on how you are connecting to Neo4j (REST or embedded) and which programming language you are using. But the idea is the same in any case.

answered Feb 05 '12 at 06:28

Josh Adell

1,555
8
12

8

Can you please explain why mixing epoch date and `ISO8601` time is a good idea? If I were to implement it I'd go with either `20131124` and `1130` OR `1385251200` and `41400` (seconds since midnight: `(11*60+30)*60`), this way you can either concatenate the parts and parse a `yyyyMMddHHmmss` format OR do `Calendar.setTimeInMillis` with the two values added together when it needs to display. – TWiStErRob Nov 24 '13 at 11:36
1

@Phil there seems to have been some progress on this, regarding GraphAware's TimeTree. Is this still the optimal way to search for times within a certain range in neo4j? This answer seems to be very, very inefficient, because I'll have to convert every value to a timestamp, and then compare that value with every other node in the graph if additional month/year complexity isn't added. – NumenorForLife Jun 12 '15 at 01:21
can this be demonstrated through a simple cypher query? – user482963 Aug 02 '18 at 13:54

score 9 · Answer 2 · answered Feb 05 '12 at 19:16

9

There's a convenient org.neo4j.index.lucene.LuceneTimeline which does this (using an integrated lucene index in neo4j).

answered Feb 05 '12 at 19:16

Mattias Finné

3,034
1
15
7

score 5 · Answer 3 · answered Feb 05 '12 at 18:16

This is an extension to Josh Adell's answer. For readability, I suggest having two date and time integer fields like

date:19970716 (YYYYMMDD)
time:203045000 (HHmmssuuu): last three digits for microseconds.

The int datatype can store upto 2147483647. If you are feeling adventurous, the long datatype can store upto 9223372036854775807. http://docs.neo4j.org/chunked/stable/graphdb-neo4j-properties.html

Inspired from ISO 8601 timestamps like 1997-07-16T19:20:30.45Z.

Disclaimer: I have only minimal experience with Neo4J.

That's millisecond not micro. – TWiStErRob Nov 24 '13 at 10:59 — TWiStErRob, Nov 24 '13 at 10:59

score 1 · Answer 4 · answered Jul 11 '14 at 20:32

with Spring data neo4j

     public List<Email> getAllEmailData(Date startDate, Date endDate) {
            List<Email> list = new ArrayList<Email>();
            if (startDate == null || endDate == null) {
                return null;
            }               
             long  first = ConversionsUtils.convertDateToLong(startDate);
             long  second = ConversionsUtils.convertDateToLong(endDate);         

            try {
                list = emailRepository.searchAllData(first, second);
               // System.out.println("List size " +list.size());
            } catch (Exception e) {
                e.printStackTrace();
            }
            return list;
        }


         @Query(
                "START  email=node:__types__(className='com.backend.core.neo.entities.Email') "            
                + "WHERE  email.searchDate > {0} and email.searchDate < {1}"
                + "RETURN email")
        List<Email> searchAllData(long startDate, long endDate);

email entity

@NodeEntity
public class Email implements Serializable {

    private static final long serialVersionUID = 1L;
    public static final String CC = "CC";
    public static final String TO = "TO";

    @GraphId
    private Long id;

    @GraphProperty
    private Long senderId;

    @GraphProperty
    private String subject;    

    @Indexed 
   // @GraphProperty(propertyType = java.util.Date.class)
    private String dateSent;

    @Indexed    
    private long searchDate;

    @GraphProperty
    private String emailTxt;
    @GraphProperty
    private String emailHtml;
    @GraphProperty
    private String emailId;
    //mail to
    @Fetch
    @RelatedTo(elementClass = User.class, type = TO, direction = Direction.OUTGOING)
    private Set<User> intoUsers;
    //mail shared
    @Fetch
    @RelatedTo(elementClass = User.class, type = CC, direction = Direction.OUTGOING)
    private Set<User> sharedUsers;

In neo4j, how can I index by date and search in a date range?

4 Answers4

Linked