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

[Ayça Erdem_OBSS_ · Accepted solution]

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

[Abhishek Ashok Dingre]

Thank you..  Yes, it helps.

[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!

[Abhishek Ashok Dingre]

Thank you .. Taken a note

[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

[Abhishek Ashok Dingre]

Thank you.. Taken a note.

[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/

[Abhishek Ashok Dingre]

Thanks .... Taken a note.

[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

[Abhishek Ashok Dingre]

Thank you

[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

[Martins Vanags - eazyBI]

Hi Abhishek,

You could give eazyBI a chance.

It can let you calculate the total and average time in the source status for the transition between two statuses. It imports the data from the issue changelog so you can calculate the difference between entering and exiting a status.

It is enough to calcualte the time issue spent in the source status before it was changed to another status.

Additionally, you can show the dates when this transition occurred and, if necessary, the users who made transitions.

Martins / eazyBI support

[Mehmet A _Bloompeak_]

Hi @Abhishek Ashok Dingre

As far as I understand, what you are looking for is a cycle time (the time it takes for the team to start working on an issue and complete it) report. The raw data you need is available in issue history and you can get it using Jira Rest API. It provides status changes for each issue by returning a json which you need to parse to calculate cycle time.

If you prefer using 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 Cycle Time for Each Issue report. You can choose the statuses that you want to include in cycle time calculation.

For further details, you can have a look at Cycle Time and Lead Time in Jira: Productivity Measurement with Two Critical Parameters article and Status Time Reports How to Videos.

App Features:

• 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.

If you have any questions, feel free to schedule a call with us.

Hope it helps.