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

Question

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_qysy8m · Answer

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.

Gökçe Gürsel {Appfire} · Answer

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] · Answer

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