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