Query to get transition tab time

[Mehak Aggarwal]

Hi

I need to retrieve the time difference between two status .
I have a workflow that has different statuses, I need to check how long was an issue in a particular status.
The information is provided in the transitions tab of the issue.
I need a query to search the database and retrieve this for number of issues in my project.
I had gone through the query provided in a thread at
https://community.atlassian.com/t5/Jira-Core-questions/Query-to-get-transition-tab-time-in-source-status/qaq-p/639818
but was not successful in running the query .
If anyone as faced a similar issue and has a running query to search the database , it would be of great help.

[Tuncay Senturk]

Hello Mehak,

IMHO, getting those values from database is not a good practice.

Those tables (changegroup and changeitem) are one of the most used tables for Jira application so that hitting database for such queries may affect your Jira performance.

At least you need to run the query from T - 1 database. Think twice before implementing such a database query.

Anyway, if you are willing to use an app Enhancer Plugin has some features to achieve that.

For instance you can use Time Between custom field measure time between two statuses or events. It is also an indexed custom field so that it won't hit database but uses Lucene index which is on application layer. You can use this custom field in any gadget so that users can see them in Jira without any need from database administrators.

There is also Status Timer field in which you can see details (drill down to status times and user times)

[Mehak Aggarwal]

Thank you for your response.

I have added custom fields and for all the new issues I am able to retrieve the transition time between statuses, but this change does not applies for already created issues in my project .

I would like to get the transition time for all the issues that were created before I have added these custom fields.

In this case database query could be helpful.

Unfortunately we do not have this plugin purchased , so need to rely on the DB query.

[Tuncay Senturk]

Hello, 

If you reindex Jira, those values will also be populated. I mean you'll have values for already existing issues after reindexing Jira/project.

Best

[Mehak Aggarwal]

We do not have this plugin in our Jira instance.

I have created custom fields similar to the one mentioned at this link:

https://community.atlassian.com/t5/Jira-questions/How-do-I-set-a-custom-field-with-the-current-date-time-in-a-post/qaq-p/143415

These values are not populated for already existing issues even after re-indexing the Jira.

Performance related issues can be ignored.

I tried using the query provided in https://community.atlassian.com/t5/Jira-Core-questions/Query-to-get-transition-tab-time-in-source-status/qaq-p/639818 but was not able to execute it successfully.

Thanks,

Mehak