Gremlin: Date filters

Question

Filter Graph DB based on date field: I searched http://tinkerpop.apache.org/docs/current/reference/ but did not find a documentation for the same.

Did some research and it seems lt, gt, etc are working. BUT is my below approach the proper way? or is there a official way to do it? Below code works on Neptune and NEO4J but is this Vendor Independent.

Also found a 4/5 yr old post where it was recommended to use long, but I think its pretty old.

Sample data:

g.addV("TestDate2").property("title", "Alpha").property("date", "01-19-2018")
g.addV("TestDate2").property("title", "Bravo").property("date", "02-20-2018")
g.addV("TestDate2").property("title", "Charlie").property("date", "03-13-2018")
g.addV("TestDate2").property("title", "Delta").property("date", "04-14-2018")
g.addV("TestDate2").property("title", "Echo").property("date", "05-15-2018")
g.addV("TestDate2").property("title", "Foxtrot").property("date", "06-16-2018")
g.addV("TestDate2").property("title", "Hotel").property("date", "07-17-2018")
g.addV("TestDate2").property("title", "India").property("date", "08-18-2018")

Queries:

(I formatted the data of output so it wont really match Gremlin output buts its more readability)

Less than

g.V().has("TestDate2", "date", lt("03-03-2018")).valueMap()
{'date': ['02-20-2018'], 'title': ['Bravo']}
{'date': ['01-19-2018'], 'title': ['Alpha']}

g.V().has("TestDate2", "date", lt("03-24-2018")).valueMap()
{'date': ['03-13-2018'], 'title': ['Charlie']}
{'date': ['02-20-2018'], 'title': ['Bravo']}
{'date': ['01-19-2018'], 'title': ['Alpha']}

Greater than

g.V().has("TestDate2", "date", gt("06-16-2018")).valueMap()
{'date': ['07-17-2018'], 'title': ['Hotel']}
{'date': ['08-18-2018'], 'title': ['India']}

g.V().has("TestDate2", "date", gte("06-16-2018")).valueMap()
{'date': ['07-17-2018'], 'title': ['Hotel']}
{'date': ['06-16-2018'], 'title': ['Foxtrot']}
{'date': ['08-18-2018'], 'title': ['India']}

Between filter

g.V().has("TestDate2", "date", between("04-01-2018", "07-01-2018")).valueMap()
{'date': ['06-16-2018'], 'title': ['Foxtrot']}
{'date': ['04-14-2018'], 'title': ['Delta']}
{'date': ['05-15-2018'], 'title': ['Echo']}

Fails, but its fine

g.V().has("TestDate2", "date", lt("3-3-2018")).valueMap()
{'date': ['03-13-2018'], 'title': ['Charlie']}
{'date': ['07-17-2018'], 'title': ['Hotel']}
{'date': ['02-20-2018'], 'title': ['Bravo']}
{'date': ['06-16-2018'], 'title': ['Foxtrot']}
{'date': ['04-14-2018'], 'title': ['Delta']}
{'date': ['08-18-2018'], 'title': ['India']}
{'date': ['01-19-2018'], 'title': ['Alpha']}
{'date': ['05-15-2018'], 'title': ['Echo']}

Can't you simply store the dates as `Date` objects? [`Date` is its own type in TinkerPop](http://tinkerpop.apache.org/docs/3.3.3/dev/io/#_date_2) and will be usually stored as a `long` in the dabase so it's easy to use and efficient in storage. — Florian Hockmann, Jul 06 '18 at 09:20
sorry I didn't follow, how do I store them as date? I use python to submit string queries to Gremlin — Srinath Ganesh, Jul 06 '18 at 09:21
For Python, it should work with [`datetime.datetime` objects](https://docs.python.org/2/library/datetime.html#datetime-objects) — Florian Hockmann, Jul 06 '18 at 10:42
thanks but I submit my queries like `client.submit(query)` as string, so I can't put objects in string — Srinath Ganesh, Jul 06 '18 at 10:43
I think perhaps the easiest way would be to store the dates as 13 or 10 digit integers (depending on the precision that you need). That should allow all the comparisons that you want to do and will be largely independent of how you need to query the graph. — Kelvin Lawrence, Jul 06 '18 at 13:12
As others mentioned, Strings are not the best data type to store dates. However, if you really want to use Strings, use `yyyy-MM-dd` (`'%Y-%m-%d'` in Python) to format the dates. — Daniel Kuppitz, Jul 06 '18 at 14:22
also I did not understand how to store it as `date` when I am submitting a query as a string — Srinath Ganesh, Jul 06 '18 at 14:24
just a minor mistake, the format should be YYYY/MM/DD for this to work fine — Srinath Ganesh, Jul 07 '18 at 04:37

score 9 · Accepted Answer · answered Jul 06 '18 at 22:14

If you are submitting Gremlin query as a String from Python (or any other language for that matter), Amazon Neptune has a custom syntax for specifying dates. You can use the function datetime() to specify your date as a String in a ISO8061 format. This syntax identifies the String as a Date object and processes it accordingly. Hence, you do not have to rely on lexicographic String comparison for comparing Dates. This is documented here.

For example, you can write your queries as String as follows:

// Insert Date as property
String insertQuery = "g.addV('TestDate2').property('title','Alpha').property('date', datetime('2018-01-19T00:00:00'))";

// Query for the date added above
String query = "g.V().hasLabel('TestDate2').has('date',gte(datetime('1929-01-01T00:00:00'))).valueMap()";

Having said that, as others have mentioned, you can also use a GLV client (submit queries using Gremlin bytecode instead of String) and use the client language's native Date implementation itself.

Gremlin: Date filters

1 Answers1