nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Accepted answers

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;

duration between stasuses.png

duration between stasuses2.png

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

All comments

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

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/

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

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

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)

2. Using Jira Automation

3. Using Time in Status Apps

Here is sample screenshot of the Duration Between Status Report;

duration between stasuses.png

duration between stasuses2.png

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

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

Знімок екрана 2025-02-08 о 00.51.37.png

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

Abhishek Ashok Dingre

Thank you.. Yes, it helps.

Abhishek Ashok Dingre

Thank you.. Taken a note.

Abhishek Ashok Dingre

Thanks .... Taken a note.

Abhishek Ashok Dingre

Thank you

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.

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

transition report.png

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

jira

jql

jqlquerybuilder

unknown-hosting-type

jira-unknown-hosting-type