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

Eric_Mertens August 16, 2017

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!

 

5 answers

1 accepted

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question

3 votes
Answer accepted
Andy Heinzer
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
April 5, 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

Deleted user January 13, 2020

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
Cristhian Benitez January 25, 2021

All anwsers to issues are buy this plugin or suscribe to this app. Its really anoying

Like # people like this
Eric Faigan September 10, 2021

My company originally had JIRA on prem and I was able to get most of what I needed with regards to analytics.  Then we moved to JIRA Cloud and the simplest thing that even a basic SAAS solution would offer, is not available to JIRA Cloud users.  You must constantly search the Atlassian Marketplace for yet another add-on to purchase.  Moreover, in most cases it's just me that needs the functionality, but I have to be fleeced and pony up an amount based on total licenses (400).  I am too lazy to look for an alternative solution and like most of you, my company is well vested with Atlassian to just abandon ship.  But I am sure there must be a better alternative out there - these guys set the bar really low.

Like # people like this
17 votes
Wojciech Izdebski April 8, 2020

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 :-)

Martin Breuer October 7, 2020
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 # people like this
Karl Becker February 23, 2022

Thank you Martin! This sure looks like the solution, and hopefully can be marked as the correct solution.

Tina Park April 21, 2022

I changed the query to a date for my purposes

AFTER "2022/03/20"

 works like a charm - thanks!

Like # people like this
Ian Rutgers June 16, 2022

with Tina's edit to the query, it worked like a charm :-)

Scott McDonald July 19, 2022

Hello @Ian Rutgers

 

New to advanced JQL here - hoping you can help me with this question.  When I want to use the days syntax, what is the different between using "-1D" and "1D"?

 

For instance, my full JQL is issuetype = "Hardware Refresh" AND status changed FROM OPEN TO ("Awaiting Shipping Notification") AFTER "1D", but it's not returning anything, even though the status on the ticket was changed yesterday.

issuetype = "Hardware Refresh" AND status changed FROM OPEN TO ("Awaiting Shipping Notification") AFTER "-1D" does return that ticket. 

 

Thank you in advance,
Scott

Ian Rutgers July 21, 2022

Hi Scott, 

Sorry for the slow response.  TBH i don't have a good answer.   I use "project = "ISSGFA" AND issuetype not in (Epic) AND NOT updated > -3d AND status in ("In Progress", "In Review") AND issuetype not in (Initiative)  to return aged stories that are -3d more than 3 days old.

I would have found a similar query in a Jira forum copied, tried and modified for my needs.

Sorry I could have been more help... that being said I do look forward to understanding what you found out.

Cheers,

Ian

Scott McDonald July 21, 2022

Thank you @Ian Rutgers 

 

This seems to have worked for me - issuetype = "Hardware Refresh" AND status changed FROM OPEN TO ("Awaiting Shipping Notification") AFTER "-1D" does return that ticket. 

My question in terms of querying is around the negative sign in front of the 1D.  What's the logical difference between 1D and -1D?   

 

Thanks you,
Scott

leyvaen.taylor July 22, 2022

Check out: https://university.atlassian.com/student/path/849533-gain-project-insights-through-jql for an excellent intro to JQL.

Simply put if I search for issues with: createddate >= -1d it will return everything created since yesterday (more than or equal to -1day ago). You could also use less than so createddate < -1d would return issues created before yesterday. 

If I search for created >= 1d it is looking into the future (everything created more than or equal to 1d in the future) so would return no results. The future ones are useful for fields like Due date for example. 

You can get started with the Jira basic search where you choose the fields (E.g. Project, Issue Type and so on) and add a date field like createddate/specify the time frame. Then you can switch to JQL view and Jira will generate the JQL for you. 

Alexis Dimitriadis August 24, 2023

Too late for the OP, but to understand the syntax, "-1d" is a time point that is exactly a day ago. The query inequalities select the interval before or after that time point.

I suppose createddate >= 1d will net you tickets created after tomorrow...

Priya R-LoPinto September 19, 2023

When would this condition be met?

     createddate >= 1d 

Everyday you run the query, it would be looking for issues created tomorrow. Won't it?

Maybe a better example is show me all releases where the release date is in the future (including today)

      fixversion in releaseDate("after 0d")

All releases where the release date is in the future (not including today)

      fixversion in releaseDate("after 1d")

All releases that were released yesterday

      fixversion in releaseDate("after 11d")

0 votes
Narender Singh August 16, 2023

HI All,

This is very useful thread. I have a question here. How to get the 'Changed date' populate in report list? any idea?

BR, Narender

0 votes
Tarun Sapra
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 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.

Eric_Mertens August 16, 2017

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

Available in Jira Cloud?

Thomas Schlegel
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 16, 2017

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

Kirk J Gould May 8, 2020

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

Like Daniel G_ Sinclair likes this
Mohammad Shafiei July 27, 2021

how Can We create a field with out ability to see it & Fill Automatically

i mean where shout i add Field ? Creat or edit or View ?( imean I dont want to see it anybody else)

Do you know the script of it to fill the Fields Automatically?

@Tarun Sapra 

Chingiz Fazliahmadov September 9, 2021

@Mohammad Shafiei  @Kirk J Gould @Eric_Mertens 

If you could fill in the required script, please share, I will be very grateful.

Like Ryan Patin likes this
0 votes
Thomas Schlegel
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
August 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.

Eric_Mertens August 16, 2017

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

RPN October 29, 2018

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.

Comments for this post are closed

Community moderators have prevented the ability to post new answers.

Post a new question