Automation: diff() not working with Sprint DateTimes

[Rebekah Myers]

My goal is to make an automation that alerts the lead if someone has been assigned work that has a larger total original estimate than time left in the Sprint.

For now, I have the automation triggered on when the Sprint value is changed. I am able to get the total amount of work in the Sprint using LookupIssues, and am able to get the endDate of the Sprint that the issue is in. I was able to find that a Sprint's endDate is already a datetime value and not just a date value, just like {{now}}.

However, using {{now.diff(triggerIssue.Sprint.endDate).hours}} results in nothing being printed in the log. I even checked this with trying to calculate the time just in the Sprint {{triggerIssue.Sprint.startDate.diff(triggerIssue.Sprint.endDate).hours}}. This also resulted in nothing being printed in the log. I have tried removing the .hours, or replacing it with the pretty option, or the minutes or seconds option, and it always prints nothing. I also tried adding toDateTime to them just in case. I know the datetimes work for startDate and endDate because those print out just fine.

This is my automation:

This is my output in the log:

I am familiar with why that sum value is so high -- I'm alright with that and can format as I need for the comparison later. I just need this diff() to actually give an output to compare with. Appreciate any help anyone can give!

[Bill Sheboy · Accepted solution]

Hi @Rebekah Myers 

For your scenario, there are several things to consider to get to a solution:

• Issues can carry-over between sprints, and so the sprint field can have multiple values
• Typically time tracking estimates are in working hours, and not calendar hours.  If you are instead using calendar hours, please clarify that info.

Okay, with this information, we can first extract one date time value for the end of the sprint, like this:

• action: create variable
  • name: varSprintEndDateTime
  • smart value: 

{{#lookupIssues.first.sprint}}{{#if(equals(state,"active"))}}{{endDate}}{{/}}{{/}}

That will grab the first issue from your lookup result, iterate over the sprint field, filter on the state to only find the current open / active sprint, and return that one's end date.

To then calculate the hours differences from now, you may use this:

{{now.diff(varSprintEndDateTime.toDate).hours}}

But that will be in calendar time, not working hours.  If you need to perform the comparison to the original estimate in working hours, perhaps try using businessDays x # working hours / day, or a more sophisticated approach to account for the actual time of day.  For example:

Working hours are 8 am to 5 pm, and "now" is 10 am, and so there are less than a full working day's hours remaining for the comparison.  Same thing for the sprint end date / time value.

Kind regards,
Bill

[Rebekah Myers]

While the issue I am testing this with only has one Sprint, there's inevitably issues somewhere with multiple due to bulk updates and such. I'll definitely keep this in mind, thank you!

[Kalyan Sattaluri · Accepted solution]

Hello @Rebekah Myers 

I believe this is a known issue.

Please see earlier discussion for example. HERE

Something to do with Sprint.endDate specifically. Rest all days smart values work as expected with diff.

Options:

1) Store triggerIssue.Sprint.endDate in a variable, convert that to date and use that to calculate the diff. Or

2) Instead of  {{now.diff(triggerIssue.Sprint.endDate).hours}} 

Try to log {{triggerIssue.Sprint.endDate.diff(now).hours}} 

If the other way works, you can get the absolute value. {{triggerIssue.Sprint.endDate.diff(now).hours.abs}} 

Hope it helps.

[Rebekah Myers]

Thanks for the response.

Unfortunately, I have tried swapping the now and the {{triggerIssue.Sprint.endDate}} before as well, which didn't work either.

I also tried storing the end date in an earlier value and it still didn't work. Making the value of the variable be {{triggerIssue.Sprint.endDate}} resulted in no change (after updating the rest of the automation) other than making it a bit more readable. Making the variable {{triggerIssue.Sprint.endDate.toDateTime}} broke it further by making even the end date alone not appear.

But isn't it already a dateTime? It looks like this in the Rest API: 

endDate: "2024-03-04T15:30:00.000Z"

Does diff() not support datetimes, only date? I would like to use dateTime in case something is added to an active Sprint.

[Kalyan Sattaluri]

Hello @Rebekah Myers 

Please also see @Bill Sheboy , especially the part about an issue having multiple sprints. So if you could have that then you have to grab the active sprint the way he has shown.

Now, if you know you have just 1 sprint per issue, as you can see in my screenshot below, when I use {{triggerIssue.Sprint.endDate.diff(now).hours}} I get a value, albeit negative which I can do absolute on and get +ve number.

Similar note, {{now.diff(triggerIssue.Sprint.endDate).days}} does not give me data.

This is the bug i was talking about, that is, even if you had issue with just 1 sprint, Sprint End date field does not work as expected.

And you dont need to convert to dateTime, Jira usually handles it fine.

[Rebekah Myers]

Interesting, thank you! This seemed to fix my problem. It's a shame -- I think the same issue happens with startDate, which means you can't use those values to calculate the full time of a sprint.