Extract date and time when status changed

Deepak Vanjani November 15, 2019

I would like to extract a list of date and time when tickets within a certain project changed from "New Issue" to "in progress" which can be clearly seen in the history.

I need a report for a particular period like between 1 Nov 2019 till 15 Nov 2019.

How can i write q query for this?

 

I have reviewed lot of articles like below but nothing helps

https://community.atlassian.com/t5/Jira-questions/Extract-date-and-time-when-status-changed/qaq-p/726746

https://community.atlassian.com/t5/Jira-questions/Extract-time-since-status-change/qaq-p/639262

https://community.atlassian.com/t5/Jira-questions/How-to-write-a-JQL-to-show-date-when-issue-status-CHANGED-FROM/qaq-p/625509

 

Please help urgently.

6 answers

1 accepted

6 votes
Answer accepted
Deepak Vanjani November 27, 2019

I was able to extract required data from Time in Status. Thank you everyone for your answers.

Sudharani Paleti June 10, 2021

I am also trying to extract Date& Time when a user story in transitioned from one status to other. But not sure how to pull the data by using Time in Status. Can you please explain. Appreciate it.

Bloompeak Support
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
June 14, 2021

Hi @Sudharani Paleti ,

It is not possible out-of-the-box in Jira. You can calculate it by coding which parses issue history rest api json for each issue. Or you can search for marketplace apps which does the same for you.

One of them is Status Time app developed by our team. It provides reports on how much time passed in each status as well as status entry dates and status transition count.

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. It has various other reports like assignee time, status entry dates, average/sum reports(eg. average in progress time per project). And all these are available as gadgets on the dashboard too.

Here is the online demo link, you can see it in action and try.

If you are looking for a free solution, you can try the limited version Status Time Free. Hope it helps.

Dalcina Duval October 14, 2021

Hi, I am actually trying to use your app now , I just set it up for my team; however I realized we didn't have the "Time in status" JIRA field set up, I went and ahead and set that up. But unfortunately it appears the Status Time Free is not pulling any data, is that because the time in status field wasnt set up before, so the app is capturing data from when I set up the field and on, and it can not pull pass data before the field was set up.... or did I miss something 

Bloompeak Support
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
October 14, 2021

Hi @Dalcina Duval ,

Status Time Free and Status Time apps do not set "time in status" field. Instead they calculate the time in each status on-the-fly when you open the reporting page or the issue detail page.

4 votes
Julia Denys _SaaSJet_ November 17, 2019

Hi @Deepak Vanjani

You could use the add-on from the Marketplace like Time in Status for Jira Cloud.

With this add-on, you could extract a time report with a list of dates and times when tickets within a certain project changed from one status to another.

Besides this Status Entrance Date report, you can also generate Time in Status, Assignee Time, Average Time, Time in Status per Date, Status Count, and Transition Count reports (read more).

I think this app will meet your needs when it comes to generating time reports.

You can also try Issue History, this add-on will show you the history of all changes that were made in issues.

3 votes
Avinash Bhagawati _Appfire_
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 15, 2019

Hi @Deepak Vanjani ,

If you already using JSU automation suite for Jira , then you will be able to configure your requirement with post functions.

1. Create Date and Time field custom field for each status and update this field with Update Any Issue Field (JSU) post function and value set as  %%CURRENT_DATETIME%% 

2.Write JQL based on custom fields and use the same in reports and gadgets.

Thanks,

Avinash

1 vote
RVS_Support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 16, 2021

Hi

For a readymade solution, you can try out our plugin,

Time in Status Reports 

The add-on provides the time in each status for the entire lifecycle of the issue (dates when status changed) and you can filter by issue type as well. You can also combine your statuses to define your time to initial work and also extract the transitions history of the issues. The main features of the app are as below

  • More than 8 types of Time in Status Reports. Excel Export available for all status reports.
    • Time in Status
    • Time with Assignee
    • Time in Status with Assignee
    • Time with Assignee per Status
    • Status/Assignee Count
    • Multiple Transition Reports
    • Avg Time reports
  • Status grouping
  • Save your reports
  • CSV Export
  • Multiple chart types

Time in Status.png

Dalcina Duval October 18, 2021

Hi, That would be awesome but it looks like this is only available for JIRA cloud, im using regular JIRA

0 votes
Zane eazyBI Support
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
October 15, 2021

Hi @Deepak Vanjani

If you do not mind using 3rd party apps, you may take a look at eazyBI reports and charts for Jira.

Out of the box, eazyBI offers a set of metrics representing issue status change history to see issue count that had status change and well as date and time of status transition:

  • Transitions to status issues count
  • Transitions from status issues count
  • Transition to status first date
  • Transition from status first date
  • Transition to status last date
  • Transition from status last date

In the report, you filter data by Time (specific period of transition), Project or user who changed issue status (Transition Author), and other issue fields (see picture below).

eazyBI status change history.png

Take a look at a few reports in the eazyBI Demo account:

Best,
Zane / support@eazyBI.com

0 votes
Tuncay Senturk
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 15, 2019

Hi @Deepak Vanjani 

You can't do that out-of-the-box. There are two methods: 

  • Use Jira REST API
  • Use an app from Atlassian Marketplace

Since, I'm one of the folks behind Snapbytes, I can recommend Enhancer Plugin for Jira for your both questions.

It has both Status Entry Date and Status Exit Date custom fields in order to extract status dates (e.g. the datetime issue's status was changed to In Progress, or the datetime issue's status changed from in progress). After defining the custom fields and indexing Jira issues, you can use these date fields in your JQL queries.

Also it provides User/Group History Report in which you can filter issues by date and user/group to get actions in that period.

I hope I was helpful

Regards

Tuncay

Suggest an answer

Log in or Sign up to answer