All Collections
Data Analysis
Gremlin
Gremlin Tricks: Working with dates
Gremlin Tricks: Working with dates
How do you calculate the difference between two dates or check if a date is X days in the past or future?
Kristine Marhilevica avatar
Written by Kristine Marhilevica
Updated over a week ago

In this article, we will look at some examples of how you can both check the duration between dates, as well as how to perform "date math" (adding x days/months to a date).

Since Gremlin queries run on top of the Groovy language, we will use the following classes to help us:

Example: Calculated date field that is 90 days after the Last Updated field

This example shows how we can create a calculated date field that is 90 days (roughly three months) after the component's Last Updated date. We can use this to know when we should review information.

utcFormat = "yyyy-MM-dd'T'HH:mm:ss.SSSX";tc = groovy.time.TimeCategory;g.V(ids).project('id', 'name', 'last updated', 'value').by(id).by('name').by('last-updated').by(  values('last-updated').  map{    Date.parse(utcFormat, it.get()).    plus(90).    format(utcFormat)  })

Note: if we want to add something other than days, things become a bit more complicated (as time usually is!).

For example, let's say we want to correctly add 3 months to the review time; since the definition of a month is dependent on when in the year you do the calculation, the query is also a bit more complex, and we need to use a "DatumDependentDuration" to perform our date addition:

utcFormat = "yyyy-MM-dd'T'HH:mm:ss.SSSX";tc = groovy.time.TimeCategory;g.V(ids).project('id', 'name', 'last updated', 'value').by(id).by('name').by('last-updated').by(  values('last-updated').  map{    lastUpdated = Date.parse(utcFormat, it.get())    threeMonths = new groovy.time.DatumDependentDuration(      0, // years      3, // months      0, // days      0, // hours      0, // minutes      0, // seconds      0  // milliseconds    )    tc.plus(lastUpdated, threeMonths).format(utcFormat)  })

Example: Days that have elapsed since application review date

In this example, we want to create a calculated field that counts how many days have elapsed since an application was reviewed.

utcFormat = "yyyy-MM-dd'T'HH:mm:ss.SSSX";today = new Date();tc = groovy.time.TimeCategory;hasValidDateValue = { field ->  values(field).  filter{    try {      Date.parse(utcFormat, it.get());      return true;    } catch (e) {      return false;    }  }}daysSince = { datePropertyKey ->  values(datePropertyKey).  map{    tc.minus(today, Date.parse(utcFormat, it.get())).days;  }}g.V().  project('id', 'name', 'value').    by(id).    by('name').    by(      choose(        filter(hasValidDateValue('review_date')),        daysSince('review_date'),        constant(1000)))

With this query, we can now add a conditional formatting filter that colorizes applications based on how many days it has been since the application was last reviewed:

ardoq formatting

With these formatting rules we can now at a glance see which applications have not been reviewed recently or at all (in the query we specified that a missing value equals 1000 days ago):

ardoq formatting rules
Did this answer your question?