Scripted field for Time in Multiple Statuses

[Donald Kelley]

I am trying to create a field called Time in Engineering using a scripted field.  I have done this based off the example script provided to get the time of a particular status.

MY problem stems from the fact that if I try to use a particular status, and only that status it does not return a value it is always blank

This status is generally the first status of any issue (however some issuetypes bypass it) but the status can also be used on a reopen or it can be sent back to from a variety of other statuses.  I know plugins can assist further with this but those options are not short terms solutions.  I can get all other statuses to report correctly.  

Any assistance would be greatly appreciated.  The script I am using is below.  The status with the issue is "Awaiting Triage"  If this status is included the return value is blank.  If it is removed from the script the sum of the other statues are returned.

import com.atlassian.core.util.DateUtils
import com.atlassian.jira.ComponentManager
import com.atlassian.jira.issue.history.ChangeItemBean

def componentManager = ComponentManager.getInstance()
def changeHistoryManager = componentManager.getChangeHistoryManager()

def inProgressName = "Awaiting Triage"
def inClosed = "Closed"
def inAssigned = "Assigned"
def inFix = "Fix"
def inRFT = "Ready for Test"
def inPromote = "Approved to Promote"
def inInvestigation = "Investigation"

def rt = [0]
changeHistoryManager.getChangeItemsForField (issue, "status").reverse().each {ChangeItemBean item ->

def timeDiff = System.currentTimeMillis() - item.created.getTime()

if (item.fromString == inAssigned) {
rt << -timeDiff
}
if (item.toString == inAssigned){
rt << timeDiff
}
if (item.fromString == inFix){
rt << -timeDiff
}
if (item.toString == inFix){
rt << timeDiff
}
if (item.fromString == inRFT){
rt << -timeDiff
}
// if (item.toString == inRFT){
// rt << timeDiff
// }
// if (item.fromString == inPromote){
// rt <<-timeDiff
// }
if (item.toString == inPromote){
rt <<timeDiff
}
if (item.fromString == inInvestigation){
rt << -timeDiff
}
if (item.toString == inInvestigation){
rt << timeDiff
}
if (item.fromString == inProgressName) {
rt << -timeDiff
}
if (item.toString == inProgressName){
rt << timeDiff
}

}

// NOTE: doesn't show anything if less than 60 seconds
DateUtils.getDurationString(Math.round(rt.sum() / 1000))

[shiva]

@Jonny Carter @Donald Kelley 

The script works!

Any chance we can count the time in week days, excluding weekends ?

[Jonny Carter]

Sure! Most Java solutions to the problem can be easily adapted to Groovy. See, for example, https://stackoverflow.com/questions/8171968/calculating-dates-given-two-dates-excluding-weekend .

The Java LocalDate API is pretty useful for things of this nature.

[Jonny Carter]

Side note: one thing I notice is that you're using the ComponentManager class to get the change history manager. Might switch to the ComponentAccessor, per the docs. Even if you're not on JIRA 7 yet, this will help future-proof you.

Anyway, the example script for determining how long something is in progress presumes that the status isn't the first one in your workflow. If that's the case, you'll need to add the time from when the issue gets created to the first time the status changes. Also, if an issue has never had its status updated, you'll need to add the time in.

Something like

import com.atlassian.core.util.DateUtils
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.history.ChangeItemBean

def issueService = ComponentAccessor.issueService
def user = ComponentAccessor.jiraAuthenticationContext.loggedInUser
def issue = issueService.getIssue(user, "SSPA-26").issue
def changeHistoryManager = ComponentAccessor.getChangeHistoryManager()
def awaitingTriageStatus  = "Awaiting Triage"
def rt = [0L]
def changeItems = changeHistoryManager.getChangeItemsForField(issue, "status")

changeItems.reverse().each { ChangeItemBean item ->
    log.debug("Change item: from ${item.fromString} to ${item.toString}")
    def timeDiff = System.currentTimeMillis() - item.created.getTime()

    if (item.fromString == awaitingTriageStatus ) {
        rt << -timeDiff
    }
    if (item.toString == awaitingTriageStatus ) {
        rt << timeDiff
    }
}

if (! changeItems) {
    rt << (System.currentTimeMillis() - issue.getCreated().getTime())
}

// NOTE: doesn't show anything if less than 60 seconds
def total = DateUtils.getDurationString(Math.round(rt.sum() / 1000))
log.debug(total)
return total

[Donald Kelley]

Thank you that worked.  What if I wanted to get something that showed me all time except for a set of statuses.  For example we have 4 statuses that we consider outside of development time and want to calculate only time in development.  Could we use something to calculate the total time and subtract time spent in a specific status?

[Isabella Frari]

/* Calculate the Sum of Durarion for time spent in group of Statuses "status1", "Status2", "Status3" append any status -- Get duration within Business Hours
*/

import com.atlassian.jira.issue.history.ChangeItemBean
import org.joda.time.DateTime
import org.joda.time.DateTimeZone

import java.sql.Timestamp
import java.time.*
import java.time.temporal.ChronoUnit
import java.util.stream.IntStream

final List<String> statuses = ["status1", "status2", "status3"]
final String statusName = "" //Any of myStatus to check
final List<ChangeItemBean> changeItems = issue.getFieldHistory("status")
if (changeItems.size() == 0) //issue status has never changed
if (issue.get("status").name == statusName)
return workingSecondsBetween(new Date().getTime(), issue.getCreated().getTime())
else
return null;
long totalTime = 0L;
Long startTime = null;
//iterate over changes
for (ChangeItemBean changeItemBean : changeItems) {
if (statuses.contains(changeItemBean.getFromString())) {
if (startTime == null) //Assuming this might have have been the starting status
startTime = issue.getCreated().getTime();
totalTime += workingSecondsBetween(changeItemBean.getCreated().getTime(), startTime);
startTime = null;
}
if (statuses.contains(changeItemBean.getToString())) {
startTime = changeItemBean.getCreated().getTime();
}
}
if (startTime != null)
totalTime += workingSecondsBetween(new Date().getTime(), startTime); //we are currently in the requested status
return totalTime

public class WorkingMinutesCalculator {
private static final int WORK_HOUR_START = 9; //first working hour in 24hr MyClock
private static final int WORK_HOUR_END = 17; //last working hour (work ends at the end of this hour)
private static final long SECONDS_PER_HOUR = 3600;

private static final long WORKING_HOURS_PER_DAY = WORK_HOUR_END - WORK_HOUR_START;
private static final long WORKING_SECONDS_PER_DAY = WORKING_HOURS_PER_DAY * SECONDS_PER_HOUR;

public int getWorkingSecondsSince(final Timestamp startTime) {
Timestamp now = Timestamp.from(Instant.now());
return getWorkingSeconds(startTime, now);
}

public int getWorkingSeconds(final Timestamp startTime, final Timestamp endTime) {
if (null == startTime || null == endTime) {
throw new IllegalStateException();
}
if (endTime.before(startTime)) {
return 0;
}

LocalDateTime from = startTime.toLocalDateTime();
LocalDateTime to = endTime.toLocalDateTime();

LocalDate fromDay = from.toLocalDate();
LocalDate toDay = to.toLocalDate();

int allDaysBetween = (int) (ChronoUnit.DAYS.between(fromDay, toDay) + 1);
long allWorkingSeconds = IntStream.range(0, allDaysBetween)
.filter{i -> isWorkingDay(from.plusDays(i))}
.count() * WORKING_SECONDS_PER_DAY ;

// MyClock from - working_day_from_start
long tailRedundantSeconds = 0;
if (isWorkingDay(from)) {
if (isWorkingHours(from)) {
tailRedundantSeconds = Duration.between(fromDay.atTime(WORK_HOUR_START, 0), from).toSeconds();
} else if (from.getHour() > WORK_HOUR_START) {
tailRedundantSeconds = WORKING_SECONDS_PER_DAY;
}
}

// MyClock working_day_end - to
long headRedundanSeconds = 0;
if (isWorkingDay(to)) {
if (isWorkingHours(to)) {
headRedundanSeconds = Duration.between(to, toDay.atTime(WORK_HOUR_END, 0)).toSeconds();
} else if (from.getHour() < WORK_HOUR_START) {
headRedundanSeconds = WORKING_SECONDS_PER_DAY;
}
}
return (int) (allWorkingSeconds - tailRedundantSeconds - headRedundanSeconds);
}

private boolean isWorkingDay(final LocalDateTime time) {
return time.getDayOfWeek().getValue() < DayOfWeek.SATURDAY.getValue();
}

private boolean isWorkingHours(final LocalDateTime time) {
int hour = time.getHour();
return WORK_HOUR_START <= hour && hour <= WORK_HOUR_END;
}
}

long workingSecondsBetween(long to, long from) {
//to and from are Unix time in the system time zone (not UTC)
final DateTimeZone timeZone = DateTimeZone.forID("America/Chicago") //adjusted for my time zone CST (Central)
DateTime fromDt = new DateTime(from + (new Date(from)).getTimezoneOffset()*60000, timeZone)
DateTime toDt = new DateTime(to + (new Date(to)).getTimezoneOffset()*60000, timeZone)
return (new WorkingMinutesCalculator()).getWorkingSeconds(new Timestamp(fromDt.toLocalDateTime().getLocalMillis()), new Timestamp(toDt.toLocalDateTime().getLocalMillis()))
}