Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,362,311
Community Members
 
Community Events
168
Community Groups

How to get time spent in certain status per issue

Edited

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

8 answers

1 accepted

1 vote
Answer accepted
laralg Rising Star Aug 21, 2018

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:

image.pngRegards

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

Like Gustavo Sant 'Anna likes this

@Pooja 

 

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

Like # people like this

Hi @Mahesh Kallepalli ,

 

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

Like # people like this

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

@Pooja  I want same

Like # people like this

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?

@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,

Like Hitesh.Kumar likes this

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

Hi there,

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

IssueScreenView-4.gif

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

worked for me, Thanks :)

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 Rising Star Feb 27, 2019

Amazing Michael! 

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? 

@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.

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.

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

Hi Michael,

I was really impressed with the tool and its featuresEJQL.PNG 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

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!

Like Mahesh Kallepalli likes this
2 votes
Mirek Community Leader Aug 21, 2018

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

Rahul_RVS_Support Marketplace Partner May 16, 2021

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)

TIS-5-Apr.PNG

Hi ,

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

Suggest an answer

Log in or Sign up to answer