JQL Query to fetch time taken from one status to another

[Abhishek Ashok Dingre]

Hi Everyone,

I have a requirement where I need to calculate how much time we took for a user story to move from "Approved by Dev" status to "Completed".

Approved by Dev status indicates that developer has agreed to pick it up and Completed status is the status when user story has been completed. I tried to use a JQL query such as 

project = "ABC" and issuetype = "User Story" AND status was "Approved by Development" AND status was in (Completed)

However, this query has 2 issues:

• It will extract data for user stories which has progressed from Completed to Accepted. Accepted is the next stage after completed.
• It does not shows how much time has spent in the transition.

How can I get the transition time between 2 status in JIRA. 

Thank you so much for your help!

Regards

[Valeriia_Havrylenko_SaaSJet]

Hi @Abhishek Ashok Dingre 
Welcome to the Community!

If you’re open to using add-ons, it is possible to calculate how much time we took for a user story to move from "Approved by Dev" status to "Completed" with Time Metrics Tracker | Time Between Statuses

Here’s how you can achieve this:

1. Create a new metric and name it 

2. Define your work schedule

3. Additionally, with the add-on, it is possible to measure metrics precisely by selecting the transition statuses you need to calculate this metric. This ensures that your reports will be highly accurate.

 Also you can book a session with our specialist to see add-on in action 🚀

Add-on developed by my SaaSJet team. 
Hope this helps!

[Madhu_RVS]

Hi @Abhishek Ashok Dingre 

Welcome to the community !!

Not sure if this information can be extracted out of the box. As suggested by everyone a mktplace app can help here. If you are open to try one, do take a look at

Time in Status Reports 

The report "Time between status transitions" can help you calculate the time between two statuses. Also there are multiple other time in status reports which the app provides to identify bottlenecks and streamline your processes. Do try it out

More details here

Disclaimer : I work for the vendor who built this app

[Rilwan Ahmed]

Hi @Abhishek Ashok Dingre ,

Welcome to the community !!

In native jira, you can get the time between 2 status. You will have to use add-ons or plugins like time in status, or by using scripts from script runner etc.

You can search for apps in https://marketplace.atlassian.com/

[Alexis Robert]

Hi @Abhishek Ashok Dingre , 

getting this information is not possible out of the box in Jira. You will need an addon to get this type of reporting, there is unfortunately no way around it.

The only workaround would be creating custom fields where an automation would add the time when the status is reached and then calculate the difference, but that works only if you don't have edge cases (coming back to a previous status, skipping a status, etc).

That's why I would recommend those 2 addons : https://marketplace.atlassian.com/apps/1219732/time-in-status?tab=overview&hosting=cloud or https://marketplace.atlassian.com/apps/1230997/time-in-status-report-dashboard-gadget-for-jira?hosting=cloud&tab=overview

Let me know if this helps, 

--Alexis

[Ayça Erdem_OBSS_]

Hello @Abhishek Ashok Dingre 

Welcome to the community!

JQL alone cannot calculate the time spent between two statuses. However, you have a few options to achieve this in Jira:

1. Using Jira’s Control Chart (Native Option)

Jira’s Control Chart (available in Kanban boards) can help visualize how long issues took to move between different statuses. However, this is more of a visual tool and does not provide exact transition durations for individual issues.

2. Using Jira Automation

You can set up Jira Automation rules to capture timestamps when an issue enters and exits a specific status. Then, use a custom field to calculate the difference. This approach requires some configuration but provides a built-in solution without external add-ons.

3. Using Time in Status Apps 

If you need a detailed breakdown of transition times, Time in Status apps like Timepiece - Time in Status for Jira, developed by our team at OBSS, can generate a Duration  Between Stasuses Report. This report calculates how long each issue spent transitioning from Approved by Dev to Completed, giving you precise time tracking.

Here is sample screenshot of the Duration Between Status Report;

If you'd like to explore this solution further, feel free to check out Timepiece on the Atlassian Marketplace or schedule a demo with us for a walkthrough of the app's features.

Hope this helps! 🚀

Ayça

[Hannes Obweger - JXL for Jira]

Hi @Abhishek Ashok Dingre

welcome to the community!

An issue's status changes are captured in an issue's issue history, which you can access from the issue view in Jira or via Jira's REST API. From these status changes, you can calculate the time that an issue spent in a particular status - however it can be quite tricky to do that by hand.

That's the reason why a lot of users rely on solutions from the Atlassian Marketplace to do that for them. E.g., you may want to have a look at the app that my team and I are working on, JXL for Jira.

JXL is a full-fledged spreadsheet/table view for your issues that allows viewing, inline-editing, sorting, and filtering by all your issue fields, much like you’d do in e.g. Excel or Google Sheets. It also comes with a long list of so-called history columns that aren't natively available, including the time in [status], time between [status] and [status], and many, many more.

This is how it looks in action:

As you can see above, you can easily sort and filter by your history columns, and also use them across JXL's advanced features, such as support for (configurable) issue hierarchies, issue grouping by any issue field(s), sum-ups, or conditional formatting. Of course, you can also export your data to Excel or CSV in just two clicks.

Any questions just let me know,

Best,

Hannes