Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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 Kononchuk Valerii 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 the 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.

Moreover, you can export all necessary data as a report in XLS or CVS file 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 and develops the ability to show an average time spend in status in the grid. So any time soon, this data can be seen directly in the application.

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

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.

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.

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

Hi ,

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

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you