1

Right now I'm designing a system with Neo4j database where I have to be able to have a query check if a Date property in a node is before, equal or after the provided Date.

How should I store the Date inside of Neo4j Node property to be able to do a comparison with Cypher query based for example on simple operators like ==, >, <

Is it okay to store Date like Long timestamp? Will it work this way ? If no, please suggest a better decision.

UPDATED

Queries I have tried with no luck:

MATCH (parentD)-[:CONTAINS]->(childD:Decision)-[ru:CREATED_BY]->(u:User) WHERE id(parentD) = {decisionId}  MATCH (childD)<-[:SET_FOR]-(filterValue153:Value)-[:SET_ON]->(filterCharacteristic153:Characteristic) WHERE id(filterCharacteristic153) = 153 WITH filterValue153, childD, ru, u WHERE  (filterValue153.value = '60305027689736') 

MATCH (parentD)-[:CONTAINS]->(childD:Decision)-[ru:CREATED_BY]->(u:User) WHERE id(parentD) = {decisionId}  MATCH (childD)<-[:SET_FOR]-(filterValue153:Value)-[:SET_ON]->(filterCharacteristic153:Characteristic) WHERE id(filterCharacteristic153) = 153 WITH filterValue153, childD, ru, u WHERE  (filterValue153.value = 'Mon Dec 27 22:35:56 EET 3880')

Where filterValue153.value has been stored like java.util.Date object in Value.value node property

@NodeEntity
public class Value extends Authorable {

    public final static String NODE_NAME = "Value";

    private final static String SET_FOR = "SET_FOR";
    private final static String SET_ON = "SET_ON";

    @Relationship(type = SET_FOR, direction = Relationship.OUTGOING)
    private Decision decision;

    @Relationship(type = SET_ON, direction = Relationship.OUTGOING)
    private Characteristic characteristic;

    private Object value;

...

}

at database level for Value node I have a following data:

id: 848013
value:  1482873001556

UPDATED

This query works fine

MATCH (parentD)-[:CONTAINS]->(childD:Decision)-[ru:CREATED_BY]->(u:User) WHERE id(parentD) = {decisionId}  MATCH (childD)<-[:SET_FOR]-(filterValue153:Value)-[:SET_ON]->(filterCharacteristic153:Characteristic) WHERE id(filterCharacteristic153) = 153 WITH filterValue153, childD, ru, u WHERE  (filterValue153.value = 60305030539682)

but how to deal with dates prior January 1, 1970, 00:00:00 GMT ? Also is it okay to apply =, >, < operations in this Cypher query on dates ?

alexanoid
  • 24,051
  • 54
  • 210
  • 410

1 Answers1

1

Related question:

However, that question dates back to 2012. Since then, we have APOC with support for date/time conversion. This lets you convert date/time values in formats described in the Javadoc of the SimpleDateFormat class.

Dates before 1970/01/01 will work, they will be simply represented with negative numbers. For example:

CALL apoc.date.parseDefault('1969-07-21 02:56:15', 's')
YIELD value

Arithmetic comparison operators (=, <>, <, ...) will work on timestamps.

Community
  • 1
  • 1
Gabor Szarnyas
  • 4,410
  • 3
  • 18
  • 42
  • Thanks for your answer. I have updated my question with a more details. Could you please tell what am I doing wrong ? Also, could you please show an example how it can be done with old and new(with APOC) approaches ? – alexanoid Dec 27 '16 at 20:46
  • If you simply return the node with this query, what result do you get? `MATCH (filterCharacteristic153:Characteristic) WHERE id(filterCharacteristic153) = 153 RETURN *` – Gabor Szarnyas Dec 27 '16 at 20:56
  • I have added output from my Neo4j for `Value` node – alexanoid Dec 27 '16 at 21:17
  • Also, I have added a query that works.. but how to deal with dates prior January 1, 1970, 00:00:00 GMT ? Also is it okay to apply =, >, < operations in this Cypher query on these dates ? – alexanoid Dec 27 '16 at 21:24
  • @alexanoid I updated my answer. How did you derive the value 60305030539682? It's a huge number (in the range of 2^45-2^46), so it might be a result of an integer overflow. This might happen if you (or some library) tried to store a negative value in a non-negative integer variable. But I'm just guessing here. – Gabor Szarnyas Dec 28 '16 at 00:07
  • Thanks. I got 60305056418844 by calling - ((Date)value).getTime() .. Is it okay ? Also, I don't use APOC because my queries are dynamic(I create them at run time) and they should work equally on all primitive types with =, >, < operations. – alexanoid Dec 28 '16 at 04:38
  • @alexanoid you need a Unix timestamp ("epoch time") - see [this question](http://stackoverflow.com/questions/25277127/java-converting-date-to-epoch-value-produces-false-output). You can use Joda time or something like `System.out.println(Calendar.getInstance().getTime().getTime() / 1000);` which prints the current epoch time (but you have to be careful with timezones). 60305056418844 seems wrong, even after dividing it by 1000, it is the epoch time for 28 Dec 3880. – Gabor Szarnyas Dec 28 '16 at 11:17
  • @alexanoid I do not get dynamic queries vs. APOC - they should work fine together. – Gabor Szarnyas Dec 28 '16 at 11:17
  • In my understanding - right now for all comparison operations I use simple =, >, < operators but in case of APOC(I may be wrong for sure) I have to use different semantics in Cypher query in order to handle Date objects.. – alexanoid Dec 28 '16 at 11:26
  • As APOC converts to timestamps, you should be able to use the comparison operators just fine - but it's best to design a small experiment to confirm that. – Gabor Szarnyas Dec 28 '16 at 12:15
  • I ran "CALL apoc.date.parseDefault('1969-07-21 02:56:15', 's') YIELD value" in the browser of the neo4j. But it does not work. The error is: There is no procedure with the name `apoc.date.parseDefault` registered for this database instance. Please ensure you've spelled the procedure name correctly and that the procedure is properly deployed. Why? (Things like CALL apoc.help("apoc") work.) – Aqqqq Apr 17 '17 at 20:00
  • @Aqqqq please open a separate question for this. – Gabor Szarnyas Apr 17 '17 at 20:01