All CollectionsData AnalysisGremlin
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?

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, // years3, // months0, // days0, // hours0, // minutes0, // seconds0 // 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:

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):