How to get time spent in certain status per issue

[Pooja]

Hi,

Is there any JQL Query to find the time spent (start date and End date) on the particular status per issue, without the help of add-ons.

Thanks in advance

[laralg · Accepted solution]

Hello Pooja,

Sadly there is no solution for just one issue on the issue navigator, but you have Gadgets with give you a little insight on that information. In your dashboard you can add the following gadgets:

Regards

[Pooja]

Hi Lara,

Thank you For your Suggestion, But I want to export it in the Excel Sheet at the end of the day, which is not possible with reports.

Regards,

Pooja

[Mahesh Kallepalli]

@Pooja 

Have you got solution for this issue , We also need same reports . Can you help me out how you get resolved this issue .

[Pooja]

Hi @Mahesh Kallepalli ,

We have not  got any solution for this, so we extracted directly from database by power BI

[anuraj_sl]

@Pooja  Can you explain how you managed to get that ?

[Yojana]

@Pooja  I want same

[Hitesh.Kumar]

Hi @Pooja - Can you share, how did you use power BI to get details for the time spent (start date and end date) on the particular status per issue, without the help of add-ons?

[Pooja]

@Hitesh.Kumar , Our DB has created read only database user access and connected with Power BI tool, Our Power BI team has integrated Jira db with Tool, not sure how,

[Julia Denys]

Hi @Pooja

Unfortunately, there is no way you can find the time spent (start date and end date) on the particular status per issue with JQL Query, but you can achieve it with Time in Status for Jira Cloud by SaaSjet.

With this tool, you can easily track the number of times an issue has been in every status. Also, you can get information about the time in status for each stage in a workflow and the number of times a task has gone from status to status.

Safe to say, this add-on is a good way to get a helicopter view, find out bottlenecks and unproductive delays in your processes.

Additionally, you'll get 7 reports that show:

• Time in Status
• Assignee Time
• Average Time
• Status Entrance Date
• Time in Status per Date
• Status Count
• Transition Count

You can view data as a Table or Chart. And use a Pivot to customize your reports as you need.

Moreover, you can export all necessary data as a report in XLS or CVS files and set up a teamwork schedule to exclude non-working hours or days. Based on this extracted custom report you can proceed with your corporate analysis.

The add-on is constantly improving. Hope, that the new Time in Status for Jira Cloud will help you to create the reports you need.

Try an online demo to understand better how it works without installing.

Best regards,
Julia
SaaSJet

[Mehmet A _Bloompeak_]

Hi there,

There is the limited but free add-on Status Time Free. It displays time spent on each status on issue screen view.

[Sanjay Dhandare]

Hi @Pooja ,

Hope this will help you !

Create one script field and comment the Mutable issue and you will get the required answer 

Note : If it solved your problem then please accept the solution ! Happy Coding

import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.history.ChangeItemBean
import com.atlassian.jira.issue.IssueManager
import com.atlassian.jira.issue.MutableIssue
import com.atlassian.jira.user.ApplicationUser
import com.atlassian.core.util.DateUtils

def changeHistoryManager = ComponentAccessor.getChangeHistoryManager()

IssueManager im = ComponentAccessor.getIssueManager()

MutableIssue issue = im.getIssueObject("TES-9")  ##getting issue 

def StatusName = "To Do" ## for To Do status

def timeNow = System.currentTimeMillis();

List<Long> rt = [0L]

def changeItems = changeHistoryManager.getChangeItemsForField(issue, "status")

def statusChanged = changeItems.size() != 0;

def currentStatusName = issue.getStatus().getName();

log.warn(" Status = "+currentStatusName)

if(!statusChanged && currentStatusName == StatusName)
{

def timeDiff = timeNow - issue.getCreated().getTime();

rt << timeDiff;
}

def changesCount = 0;

changeItems.each { ChangeItemBean item ->

def timeDiff = timeNow - item.created.getTime();

if ((item.fromString == StatusName))
{
if(changesCount == 0)
{
rt << item.created.getTime() - issue.getCreated().getTime();
}
else
{
rt << -timeDiff
}
}
if(item.toString == StatusName)
{
rt << timeDiff
}

changesCount++;
}

def total = rt.sum() /1000 as Long

def mytotal =DateUtils.getDurationString(total)

log.warn("Time Spent In Given Status == "+mytotal)

return mytotal as String ?: 0L

[Ganga Bopaiah]

worked for me, Thanks :)

[Amit Kumar Singh]

This is very helpful, but i am getting only 0m, for every issue..

[Michael Boumansour]

I developed a tool in Google Sheets that allows you to do exactly that.  I made the tool freely available.  Here is my blog post that provides the details.

[laralg]

Amazing Michael! 

[Michael Boumansour]

Thank you Lara!

[Yolanda Repetto-Logan]

Looks very comprehensive. I don't know that I can use it for security reasons. Does the resource access save the JIRA creds, or each time I initialize does it calls the creds I specify? 

[Michael Boumansour]

@Yolanda Repetto-Logan , sorry I did not see your question. The creds are saved in the User Properties of the Google Sheet so only that specific user has access to them.

[Carrie Hunt]

Having difficulties even getting the EJQL tool to connect to my jira instance - we just moved our instance behind IAP from GCP and top it with Okta for managing logins.  The only error I get everytime I do anything that requires touching JIRA is an error message that says "SyntaxError: Unexpected token: <" and I don't know what to do with that.

[Michael Boumansour]

The EJQT uses basic authentication. It does not have an option for SSL unfortunately.

[Sunil Kirangi]

Hi Michael,

I was really impressed with the tool and its features but i am not sure this is compatible with Server version of JIRA, i could not able to authenticate for Filters, do we need to configure API details in App configurations,

Can you please help me with this issue.

Thanks

Sunil Kirangi

[Caroline Calhoun]

Michael,

Thank you so, SO much for the tool, for the documentation, and for making it available. I spent days banging my head against a wall trying to figure out cycle times and in desperation finally found this thread just as I was ready to give up. My team owes you a debt of gratitude for providing us with these insights.

Thank you thank you thank you!

[Mirek]

Nope. Unfortunately without addons (or development) this is not possible to achieve in JQL (or in JIRA)

[Rahul_RVS]

Hi

If you would be interested in a ready made solution, You may want to try out the plugin, Agile Tools : Epic Tree & Time in Status 

It provides 3 major functionalities in one add-on

• More than 7 types of Time in Status Reports
  • Time in Status
  • Time with Assignee
  • Time in Status with Assignee
  • Time with Assignee per Status
  • Status/Assignee Count
  • Multiple Transition Reports
  • Avg Time reports
• Epic Hierarchy / Sum Up (Standard Jira Hierarchy Epic -> Story -> SubTask)
• Link Hierarchy / Sum Up (Hierarchy based on your issue link, upto 10 level deep)

[Aleksandar Salevski]

Hi ,

Is there a way to do this with, AIO reports?

[Swapan Swaroop]

Hello Guys,

Check this out: 

https://wiki.almworks.com/display/structure/Documentation