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:
To refer to fields using gremlin, we must use their API name. The API name of a can be found under 'Name' inside the field's properties:
The API name of a Date Range field is the value under 'Name' in its properties + '_start_date' for the Start Date. Similarly + '_end_date' for the End Date.
To get the start date for the Date Range field 'Live', the API name would be 'live_start_date'. The end date would be 'live_end_date'.
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):
