scripted field - time spent in status - translation from DC to Cloud

[Teodor Hoza]

Hello, I've tried to translate the script that shows how much time a ticket stayed in a specific status.

It does the Job, but I'd like a second opinion from someone who really is an expert to let me know if I've done it right.

Reference: https://library.adaptavist.com/entity/count-the-time-an-issue-was-in-a-particular-status

Script is as follows: 
----------------------------------------Begin script----------------------------------------

import java.time.ZonedDateTime
import java.time.format.DateTimeFormatter
import java.text.SimpleDateFormat;
import java.util.Date;
// Status to be counted
def statusName = "In Progress"
// Jira datetime field format
def formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss.SSSZ")

def changeHistoryManager = get("/rest/api/3/issue/${issue.key}/changelog")
.asObject(Map).body.values.findAll{it.items.field.contains("status")}
def totalStatusTime = [0L]
// Every status change is checked
def changeItems = changeHistoryManager as List<String,String>
logger.info("Change Items list is ${changeItems}")
changeItems.reverse().each { item ->
def createdtime = ZonedDateTime.parse((item.created),formatter).toDate()
logger.info("Created Time is ${createdtime}")
def timeDiff = System.currentTimeMillis() - createdtime.time
logger.info("Time Difference is ${timeDiff}")
String fromString = "${item.items.fromString}"
logger.info ("${fromString}")
// Subtract time if the "from" status is equal to the status to be checked and from and to statuses are different.
// This allows to count the time the issue is in the state for the first time
if (item.items.fromString.contains(statusName) && item.items.fromString != item.items.toString) {
totalStatusTime << -timeDiff
}
// Add time if the "to" status is equal to the status to be checked
if (item.items.toString.contains(statusName)) {
totalStatusTime << timeDiff
}
}

def total = totalStatusTime.sum() as Long
// Every time (added or subtracted) is summed and divided by 1000 to get seconds
(total / 1000) as long ?: 0L

----------------------------------------END script----------------------------------------

[Teodor Hoza · Accepted solution]

Small upgrade, fixed the issue where the 1st status time was showing negative values.

-------------------------------begins scripts-------------------------------

import java.time.ZonedDateTime
import java.text.SimpleDateFormat
import java.util.Date
// Status to be counted
def statusName = "In Progress"
def statusNameCheck = "[${statusName}]"
// Jira date timestamp field format
def timePattern = "yyyy-MM-dd'T'HH:mm:ss.SSSZ"
//make current time to long number in miliseconds
long currentTime = System.currentTimeMillis()
//get issuedata thrugh API
def issuedata = get("/rest/api/3/issue/${issue.key}?expand=changelog")
.asObject(Map).body
//filter changelogs into list
def statusChanges = issuedata.changelog.histories.findAll{it.items.field.contains("status")}
//get current time and format
def issueCreatedDate = new SimpleDateFormat(timePattern).parse(issuedata.fields.created)
//create a list of long numbers and add 0 as 1st item
List<Long> totalStatusTime = [0]
//calculate time difference between current time and issue created time
def createdDateDiff = System.currentTimeMillis() - issueCreatedDate.getTime()
//add time difference to totalStatusTime list with shift left for 1st status in workflow
if (statusName == "New"){totalStatusTime << createdDateDiff}
//convert statusChanges to a list of strings
def changeItems = statusChanges as List<String,String>
// Every status change is checked
changeItems.reverse().each { item ->
def createdDate = new SimpleDateFormat(timePattern).parse(item.created).getTime()
long timeDiff = currentTime - createdDate
logger.info("Time Difference is ${timeDiff}")
//extract from Status
def fromString = "${item.items.fromString}"
//extract to Status
def toString = "${item.items.toString}"
// Subtract time if the "from" status is equal to the status to be checked and from and to statuses are different.
// This allows to count the time the issue is in the state for the first time
if (fromString == statusNameCheck && fromString != toString) {
totalStatusTime << -timeDiff
}
// Add time if the "to" status is equal to the status to be checked
if (toString == statusNameCheck) {
totalStatusTime << timeDiff
}
}

def total = totalStatusTime.sum() as Long
// Every time (added or subtracted) is summed and divided by 1000 to get seconds
total / 1000 as Long ?: 0

-------------------------------ends scripts-------------------------------

[Bianca Fialho]

Hello everyone, this is being very helpful to me at the moment!

I have one question though... this return a value in seconds. Is there a way to get a 'pretty' time format out of this? Like 4d 2h 5m for example?

Thank you!

[Teodor Hoza]

Hi @Bianca Fialho ,

Add these lines to your code:

import java.util.concurrent.TimeUnit ->  add it after line 3 to import the Time Unit Java class

and replace "total / 1000 as Long ?: 0" with following lines.

long DD = TimeUnit.MILLISECONDS.toDays(total)
long HH = TimeUnit.MILLISECONDS.toHours(total) %24
long MM = TimeUnit.MILLISECONDS.toMinutes(total) % 60
long SS = TimeUnit.MILLISECONDS.toSeconds(total) % 60
String prettyDuration = String.format("%02dd %02dh %02dm %02ds",DD, HH, MM, SS)

This will do it.

[Bianca Fialho]

Wow that works amazing, thank you so much for the complete and speedy answer!!

You, sir, are awesome!

[Teodor Hoza]

Glad I could help!

[Dinesh Loyapalli]

I am receiving the below error when testing this script against an issue. Can you please help in fixing it.

Thank you!

org.codehaus.groovy.control.MultipleCompilationErrorsException: startup failed: Script_4072f4cb070da628fd4b01327e41c1aa.groovy: 26: The class java.util.List<java.lang.String, java.lang.String> (supplied with 2 type parameters) refers to the class java.util.List<E> which takes 1 parameter @ line 26, column 36.

def changeItems = statusChanges as List<String,String>

1 error at com.adaptavist.sr.cloud.workflow.AbstractScript.parseScript(AbstractScript.groovy:32) at com.adaptavist.sr.cloud.workflow.AbstractScript.evaluate(AbstractScript.groovy:26) at com.adaptavist.sr.cloud.events.ScriptedFieldExecution.run(ScriptedFieldExecution.groovy:42) at TestScriptedFieldExecution1_groovyProxy.run(Unknown Source)

[Teodor Hoza]

@Dinesh Loyapalli 

Try this:

<----- Begin----->

import java.time.ZonedDateTime;

import java.text.SimpleDateFormat;

import java.util.Date;

import java.util.concurrent.TimeUnit;

// Status to be counted

def statusName = "In Progress";

def statusNameCheck = "[${statusName}]";

// Jira date timestamp field format

def timePattern = "yyyy-MM-dd'T'HH:mm:ss.SSSZ";

// Get current time in milliseconds

long currentTime = System.currentTimeMillis();

// Get issue data through API

def issuedata = get("/rest/api/3/issue/${issue.key}?expand=changelog")

                 .asObject(Map).body;

// Filter changelogs into list

def statusChanges = issuedata.changelog.histories.findAll { it.items.field.contains("status") };

// Get issue created date and format

def issueCreatedDate = new SimpleDateFormat(timePattern).parse(issuedata.fields.created);

// Create a list of long numbers and add 0 as the first item

List<Long> totalStatusTime = [0];

// Calculate time difference between current time and issue created time

def createdDateDiff = System.currentTimeMillis() - issueCreatedDate.getTime();

// Add time difference to totalStatusTime list for the first status in workflow

if (statusName == "New") {

    totalStatusTime << createdDateDiff;

}

// Convert statusChanges to a list of strings

def changeItems = statusChanges as List<String>;

// Reverse the list and check each status change

changeItems.reverse().each { item ->

    // Get time when the change was made

    def createdDate = new SimpleDateFormat(timePattern).parse(item.created).getTime();

    long timeDiff = currentTime - createdDate;

    logger.info("Time Difference is ${timeDiff}");

    // Extract 'from' status

    def fromString = "${item.items.fromString}";

    // Extract 'to' status

    def toString = "${item.items.toString}";

    // Subtract time if the 'from' status matches the status to be checked and statuses are different

    if (fromString == statusNameCheck && fromString != toString) {

        totalStatusTime << -timeDiff;

    }

    // Add time if the 'to' status matches the status to be checked

    if (toString == statusNameCheck) {

        totalStatusTime << timeDiff;

    }

}

// Sum up the total time and convert it into days, hours, minutes, and seconds

def total = totalStatusTime.sum() as Long;

long DD = TimeUnit.MILLISECONDS.toDays(total);

long HH = TimeUnit.MILLISECONDS.toHours(total) % 24;

long MM = TimeUnit.MILLISECONDS.toMinutes(total) % 60;

long SS = TimeUnit.MILLISECONDS.toSeconds(total) % 60;

// Create a readable string for the duration

String prettyDuration = String.format("%02dd %02dh %02dm %02ds", DD, HH, MM, SS);

<----- End----->

P.S do not forget to put your 1st status of your workflow at line 32.

[Dinesh Loyapalli]

Hi Teodor,

Thank you!

It works, but I’d like to know whether it skips weekends when calculating the days in a status. Also, if the issue has been transitioned to "In Progress" multiple times, does it consider the total time spent in that status?

Thanks again for your help!

[Teodor Hoza]

Hi @Dinesh Loyapalli this does not skip weekends, but it does count each time your status has been in progress.

[Valeriia_Havrylenko_SaaSJet]

Hi @Dinesh Loyapalli  👋

If you open to third-party add-ons to help, you can try Time in Status developed by my team.

The algorithm for excluding weekends when calculating the time spent in a status for an issue is simply that you must set up your work calendar - including days off, breaks, holidays, etc.

You can also specify the time period needed for the calculation.

You can also book a live demo or contact us at Support - we'll show you the application inside out and answer all your questions.

I hope you find this helpful 🚀

[Teodor Hoza]

Yep, this is a good one, as it does solve most of my issues to create reports. I am also using it.

[Bianca Fialho]

Sorry, I have one more question as I'm having the same issue you fixed in yours of negative numbers showing for some statuses.

//add time difference to totalStatusTime list with shift left for 1st status in workflow
if (statusName == "New"){totalStatusTime << createdDateDiff}

What does "New" stand for in this line? Should I replace it with the name of the first status in my workflow? 

Thanks again for the awesome code!

[Teodor Hoza]

In order for this to work, you need to replace New with the 1st status from your workflow.

if (statusName == "First status"){totalStatusTime << createdDateDiff}

or you can define a new variable that fetches your 1st status in your workflow by using the transition rest endpoint under the issues class.

[Dinesh Loyapalli]

Can you please help me with the working code that calculates the number of days an Issue has been in a particular status?

The above code is not working for me and it is giving me an error.

Thank  you!