Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Next challenges

Recent achievements

  • Global
  • Personal

Recognition

  • Give kudos
  • Received
  • Given

Leaderboard

  • Global

Trophy case

Kudos (beta program)

Kudos logo

You've been invited into the Kudos (beta program) private group. Chat with others in the program, or give feedback to Atlassian.

View group

It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

How to write a JQL to show date when issue status CHANGED FROM "X" TO "Z"

I know that the following JQL :

status CHANGED FROM "X" to "Z" AND status = "Z" 

gives me all issues i currently need for my report.

However i would like to show the specific date the status changed from X to Z.

In my query result i get 'created date' and 'updated date' but those dates differ from the date the status changed.

Anyone can assist?

 

Thx!

 

4 answers

1 accepted

0 votes
Answer accepted
Andy Heinzer Atlassian Team Apr 05, 2019

Hi everyone,

I understand that Eric has asked for a way to easily find the date that an issue transitioned from one status into another. While JQL does have historical searching for terms like 'Changed' or 'during', these are limited in how much then can help here because an issue can potentially execute the same transition more than once.  Since JQL is designed to return Jira issues, there is not a way to search for this data in JQL alone, with native Jira at least. Thomas has suggested using a custom field to record the date when this transition happened, which could be helpful in some cases when setting up a project, but isn't helpful for existing projects without this setup yet. 

Tarun has also suggested using a 3rd party plugin for Jira like Scriptrunner that might be helpful here.   If you're wanting to create a JQL with this information for the sake of using that filter in a board or dashboard, then I would recommend trying Thomas or Tarun's suggestions here.

However if you don't need this as a saved filter, and just want to find the specific times when transitioned happened for an issue, perhaps this approach will help:


For Jira Server users, there is a way to find this data, but you have to run some SQL queries against your database to find clear dates for this kind of transition. Check out the KB https://confluence.atlassian.com/jirakb/how-to-obtain-issue-change-history-from-database-933695139.html It provides some SQL queries you could use to find specific transitions and the times they happen to issues in a Jira Server environment.  This might not help for all use cases, but if you want to find when issues changed a status value, this KB can help with that.

 

For Jira Cloud, well, I would like to apologize that there isn't a great way to do this in Cloud right now. In Jira Cloud, you don't have access to the SQL database, so you can't use the same steps above (unless you were to take a full XML backup of Cloud and import that to a Jira Server site, which is NOT as trivial as it might sound.) But I did find a marketplace solution that might help in https://marketplace.atlassian.com/apps/1217474/advanced-export?hosting=cloud&tab=overview appears it would let you export this kind of transition data into a CSV or XLS export file.  From there, you could probably then use some spreadsheet filtering to more easily locate this information against those issues. Perhaps this would help as a solution to finding that information in Jira Cloud.

Regards,
Andy

And when might one expect to be able to carry this out in Jira Cloud?

 

Seriously...why does everything useful need to be paid like a vig to a Mafioso?

Like # people like this

As a workaround you can manually specify date of change like in example below.

If the raport is for short period is should work

status CHANGED FROM "X" TO "Z" on 2020-04-03

Works also on Jira Cloud :-)

status changed to ("In QA", "IN PROGRESS") AFTER "-1w"

Works as well like charm, so you can easily list issues that, for example, reached a status in the past time range 

Like Wojciech Izdebski likes this
0 votes
Thomas Schlegel Community Leader Aug 16, 2017

Hi Eric,

you can't get this date with JQL.

If you want to have this for future issues, you can add a custom field to your project and fill this custom field with the current date in a workflow post function of the status change from X to Z. So the date is persisted and you can query it.

This sounds like a option worth a try....thx!

If it is not possible via JQL, is there another option/interface (REST, Sql/Database) to get the Status-Changed DateTimes from Jira? We need it to print velocity/process reports.

0 votes
Tarun Sapra Community Leader Aug 16, 2017

As Thomas as mentioned about using post-functions for populating date fields. Similary for all the issues upto now in your JIRA instance if you want to display the transition date then just add the "Scripted field" from the "Script runner" add=on. This will show the date of the transition from older issues as well in the futre issues and this field can be displayed in the issue navigator of the search result.

Where can i find the "Scripted field" from the "Script runner" ?

Available in Jira Cloud?

Thomas Schlegel Community Leader Aug 16, 2017

Scriptrunner is a paid plugin for Jira. If you didn't buy it, you won't see the field.

Yes, that is true.  But we have purchased Scriptrunner so how might we find it?

Suggest an answer

Log in or Sign up to answer
TAGS

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you