JQL query for finding all issues took more than x day changed from In Progress to Resolved

[codingouch]

Hi guys,

I'm wondering if I can extract the time in a certain status. For example, I'd like to filter the bugs that took more than 20 days to change from "In Progress" to " Resolved".

However, the current way I saw is all about filter the status that's still not changed. Like "not status changed after -20d". The problem is that I want to filter the bugs that current status is changed to "Resolved".

I don't know if I'm clear enough to make you guys understand. Is there any JQL query for this?

Thanks!

[Mariana_Pryshliak_Saasjet]

Hello @codingouch 

You can try Time between Statuses add-on, which is available for a server version. It generates a report on how long it takes an issue to move from one status to another (for example, how long it takes to move from "In Progress" to " Resolved").

Also, you can configure Warning and Critical Time limits for each Status Group.

I hope you find it helpful.

Best regards, Mariana.

[Bloompeak Support]

Hi @codingouch ,

Unfortunately it is not possible out-of-the-box with JQL. JQL does not have capability to compute time spent between statuses. However, the data you need is implicitly available in issue history. You can get issue history via Jira rest api and then you need parse the json response(similar to the below), calculate the time difference for each issue.

Or, you can try marketplace apps. We built Status Time app for this exact need. It provides reports on how much time spent in each status as well as status entry dates and status transition count. It also has export functionality, so that in excel you can filter the issues which took more than 20 days.

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. It has various other reports like assignee time, status entry dates, average/sum reports(eg. average in progress time per project).

Here is the online demo link, you can see it in action and try. Hope it helps.

[Gökçe Gürsel {Appfire}]

Hi @codingouch ,

You can use Enhancer plugin for Jira to identify such issues.

Enhancer Plugin for Jira has Time Between and Time in Status custom fields that measures the time spent between two statuses/events and time spent in specific status. Time in status field gives you the drill-down information based on assignee.

These fields are sortable/searchable and has gadget to assist you in identifying bottleneck issues. 

Enhancer Plugin for Jira also has many more features such as custom conditions, validators,post functions, user/group custom fields and so much more.

Please let me know if you have further questions.

Cheers,

Gökçe

Please note that I'm a part of the Snapbytes team.

[Emre Toptancı _OBSS_]

Hello @codingouch ,

Unfortunately, JQL cannot do this out of the box. JQL can filter issues but it cannot compare dates on an issue or calculate duration between dates.

I know this is not what you ask for but you can run queries to compare dates with current date. For example this query returns all issues that were created less than 20 days ago. (20 days x 24 hours ago)

project = ABC and created > -20d

You can do the same for status transitions like this:

project = ABC and status changed FROM ToDo TO Done AFTER -20d

This query will return all the issues in project ABC that were transitioned from "Todo" to "Done" in less than 20 days ago. 

What I explained above is possible but I am afraid JQL has no capability to measure time between status transitions. You will have to use issue histories to calculate that yourself or use an app for that.

For a ready built solution that offers great fexibility and details, our team at OBSS built Time in Status app for this exact need. It is available for Jira Server, Cloud and Data Center.

Time in Status allows you to see how much time each issue spent on each status or assigned to each assignee as well as entry/exit dates for all statuses. You can calculate averages and sums of those durations and count grouped by issue fields you select. (For example see the average InProgress time per project and per issuetype). You can also combine statuses into consolidated columns to see metrics like Ticket Age, Cycle Time or Lead Time.

   

The app calculates its reports using already existing Jira issue histories so when you install the app, you don't need to add anything to your issue workflows and you can get reports on your past issues as well.

The app has Custom calendar support which means you can get your reports based on a 24/7 calendar or your custom business calendar. (This one is important because a 24/7 calendar in most cases shows misleading data. For example an issue created at 16:00 on Friday and was resolved at 09:00 on next Monday seems to stay open for 2,5 days but in terms of business hours, it is only a few hours. You can see this using Time in Status by OBSS.)

Using Time in Status you can:

• See how much time each issue spent on each status, assignee, user group and also see dates of status transitions.
• Calculate averages and sums of those durations grouped by issue fields you select. (For example see average InProgress time per project and per issuetype.)
• Export your data as XLS, XLSX or CSV.
• Access data via REST API.
• Visualize data with various chart types.

https://marketplace.atlassian.com/1211756

We've introduced the dashboard gadget to Time in Status cloud version and will beadding the gadget to the server version soon.

EmreT