Find tickets by time elapsed between status transition

[gilberto.taccari]

I need to find tickets that are slow to be completed and I would like to do that by using JQL, filtering by the time a ticket is stuck in a status.

Looking online I've found that I can use the operator CHANGE and a relative time interval. I came out with the following query.

project = "OPS"
  AND status CHANGED FROM "To do" TO "In progress" BEFORE -30m
  ORDER BY created DESC

My intent is looking for the tasks that were in "To do" for at maximum 30 minutes, but the query, even if it is correct, doesn't return tickets that match this requirement. Is there an error on it?

[Valeriia_Havrylenko_SaaSJet]

Hello @gilberto.taccari 👋

Unfortunately, Jira doesn't show answers for a query to select issues based on how much time they spent in a Status. 

Try out Time between Statuses add-on, which helps you identify any slow to be completed issues by highlighting feature. 

 

Set up Warning time limits and Critical time limit easily. If too much Time is spent on a specific issue, the system will send an email notification to you or any other user when a ticket is stuck in a status.

The add-on has a 30-day free trial version and is free for up to 10 users. 

Please let me know if you have any questions

Hope it helps 😌

[Amay Purohit_RVS]

Hi @gilberto.taccari 

As suggested by others an app can help you get this data. You can take a look at the one we have built

Time in Status Reports 

You can track the time taken in each status for the issues with multiple types of reports, graphs , gadgets, status grouping and lot more.

Disclaimer : I work for RVS, the vendor for this app

[Mehmet A _Bloompeak_]

Hi @gilberto.taccari

Unfortunately, it is not possible to extract Time in Status info with built-in JQL.

If you prefer to use a marketplace app, you can try Status Time Reports app developed by our team. It mainly provides reports and gadgets based on how much time passed in each status.

Here is the online demo link, you can see it in action and try without installing the app. For your case, you can have a look at Time in Status for Each Issue report. For further details, you can have a look at Status Time Reports How to Videos.

• This app has a dynamic status grouping feature so that you can generate various valuable reports as time in status, time in assignee, status entry dates and status counts, cycle time and lead time, average/sum reports by any field(e.g. average in progress time by project, average cycle time by issue creation month).

• You can search issues by Project, Issue Type, Status, Assignee, Issue Creation/Resolution Date(and any other Date field) and JQL Query.
• Status durations are calculated according to the working calendar you define. Once you enter your working calendar into the app, it takes your working schedule into account too. That is, "In Progress" time of an issue opened on Friday at 5 PM and closed on Monday at 9 AM, will be a few hours rather than 3 days.
• You can set different duration formats.
• You can export reports in CSV file format and open them in MS Excel.
• You can also add this app as a gadget to your Jira dashboards and reach “Status Time” from Issue Detail page.
• You can enable/disable access to Status Time reports&gadgets and Issue Detail page per project, users, groups or project role.

If you are looking for a completely free solution, you can try the limited version Status Time Reports Free.

Hope it helps.

[Trudy Claspill]

Hello @gilberto.taccari 

The relative time in your query is relative to now. The query says to find issues changed from To Do to In Progress more than 30 minutes ago. That time information is not applied to the duration of time between the status changes.

Jira doesn't have any native support for creating the JQL that you want - a query to select issues based on how much time they spent in a Status.

There are third party apps that can help you do that. Here is a query for the Atlassian Marketplace to help you get started finding those:

https://marketplace.atlassian.com/search?hosting=cloud&product=jira&query=time%20in%20status