Search Exact Issues which are Staying in a Specific Status for 90 days?

[Digvijay Singh Gehlot]

Hi Community,

I want to search those exact issues which are landed to a specific status (Open) and staying are there for 90 days.

So that, I can send an email notifying to the Reporter, to move issue for further process.

I tried configuring Jira automation rule with:

Trigger: Scheduled

Condition: If Status equals - Open

And: Issue type - Story

Then: Send email - Reporter

In Scheduled component, I am using:

- Fixed rate of every 90 days

- Run a JQL: project = TEST AND issuetype = Story AND status = Open AND NOT status CHANGED AFTER -90d

Enabled "Only include that have changed since the last time this rule executed"

Enabled "Process all issues produced by this trigger in bulk"
However, while looking to my JQL, it is showing all the issue before 90 days along with the issue which actually 90 days old.

Need further guidance on how to make the correct JQL while searching for the exact issue which only 90 days old from the current date.
Any suggest on how to achieve this via Scriptrunner, JSM, Workflow Conditions/Post-functions?

Thanks

[Valeriia_Havrylenko_SaaSJet]

Hello @Digvijay Singh Gehlot ,

Since Jira’s native JQL does not support exact duration conditions (e.g., "status = Open for exactly 90 days"), we need a workaround.

Your current JQL query:

 project = TEST AND issuetype = Story AND status = Open AND NOT status CHANGED AFTER -90d

This query returns all issues that have been in "Open" for at least 90 days, not exactly 90 days.

To find issues that have been in "Open" for exactly 90 days, modify the JQL:

project = TEST AND issuetype = Story AND status = Open 
AND status CHANGED TO Open ON startOfDay(-90d)

• This ensures the issue transitioned to "Open" exactly 90 days ago.
• If you want to check a range of days (e.g., issues that have been in Open between 90-95 days), use:

project = TEST AND issuetype = Story AND status = Open 
AND status CHANGED TO Open >= startOfDay(-95d)
AND status CHANGED TO Open < startOfDay(-90d)

You can use JQL to find issues that have been in the same status for a specific time range.

Here’s how you can do it:

JQL Query: To find issues that have been in the status "X" for more 90 days, you can use:

status = "X" AND statusChangedDate >= -90d

This query only works if the issue hasn’t transitioned out of status "X" within the specified time frame. Jira doesn’t natively track cumulative time spent in a status.

Alternative Solution:

If you are interested in time reports and ope to add-ons, option for you can be Time in Status. To show report when a user story is moved from "To Do" into "in Progress", just:

1. Choose Status Entrance Date report
2. Filter by JQL 
3. Filter issues by Data Ranges
4. Get your data!

You can also book a live session to ask any questions about the add-on. Add-on developed by my team. 

I hope you find this helpful 🚀

[Digvijay Singh Gehlot]

Thank you everyone for sharing your valuable insights regarding my request.

I done my further analysis after going through each of your solutions, and tested out a JQL as below:

project = TEST AND issuetype = "Story" AND status = "In Progress" AND (status CHANGED TO 'In Progress' AFTER startOfDay(-90) AND NOT status CHANGED TO 'In Progress' AFTER endOfDay(-90))

As per my observation:

• status CHANGED TO 'In Progress' AFTER startOfDay(-90) : Ensures the issue moved to "In Progress" at least 90 days ago
• NOT status CHANGED TO 'In Progress' AFTER endOfDay(-90) : Ensures no further status changes happened in the last 90 days

For example:

• The TEST-1, which was falling under the JQL, but when user moved the issue to previous status and then moved back to "In Progress", then TEST-1 is not appearing in the same JQL search.
• This is what I was expecting that once there is status change on the issue (although it was earlier appearing in the mentioned JQL), it should not appear again in the same JQL.
• And in this case, I don't want to send email to Reporter of TEST-1 via Jira automation

Please let me know if my assumption is correctly here.

Thanks

[Rahul_RVS]

Hi @Digvijay Singh Gehlot 

You can refer to the below article for various JQL options.

Jira Time in Status JQL

However, JQL has no built-in functions to calculate time spent in statuses. This limitation makes it challenging to generate detailed time-in-status reports without additional tools or workarounds. If you are open to try out a mktplace app for this use case, you can have a look at

Time in Status Reports 

With this app you generate time in each workflow status for multiple issues with multiple filter and grouping options. For your specific requirement, the status duration filter allows to filter the issues which are in a particular status with greater than "xx" days.

More details here.

Disclaimer : I am part of the app team for this add-on

[Gizem Gökçe _OBSS_]

Hello @Digvijay Singh Gehlot ,

You can use JQL to find issues that have been in the same status for a specific time range.

Here’s how you can do it:

JQL Query: To find issues that have been in the status "X" for more 90 days, you can use:

status = "X" AND statusChangedDate >= -90d

This query only works if the issue hasn’t transitioned out of status "X" within the specified time frame. Jira doesn’t natively track cumulative time spent in a status.

Alternative Solution:
If you need more flexibility, such as tracking the cumulative time spent in a status across transitions, you can use Timepiece - Time in Status for Jira, the oldest and leading 'Time in Status' app in the Atlassian Marketplace, which is developed by my team at OBSS.

With Timepiece, you can:

• Generate a Status Duration Report to see how long each ticket has spent in specific statuses, even if it transitioned multiple times.
• Filter issues that match your criteria

 

The app calculates its reports using already existing Jira issue histories so when you install the app, you don't need to add anything to your issue workflows and you can get reports on your past issues as well. Reports are available via the reporting page, dashboard gadgets, and issue view screen tabs, with both data tables and charts. Timepiece also offers a REST API for seamless integration and supports CSV/XLS exports.

Timepiece is available for both Jira Cloud and Data Center. Let me know if you’d like more information or assistance with your reporting needs! If you wish, you can also schedule a live demo. We will provide a comprehensive overview of the application and address any inquiries you may have.

Best regards,
Gizem

[Petru Simion _Simitech Ltd__]

Hi @Digvijay Singh Gehlot ,

One solution is to write a scripted field, assuming you have Script Runner, that will calculate the time the issue spent in the current status, by parsing the history of the issue. Then you write a JQL to return all issues for a specific status where the value of your scripted field is greater than 90 days.

The solution is non trivial and will require a significant time to design, code and test.

You also have to clariy if what to do if the issue was moved out and then back into the status.

If you are open to using apps, you can use Time in Status Dashboard for Jira , an app released by our company. 

In a tabular format you can find how long issues have spent in each status and how many times they were in the status. Here is an example for status To Do.

Using the Colums perspective:

Using the Rows perspective:

Regards, 

Petru

The table shows you also how many times the issue was in the status.

[Stephen_Lugton]

Hi @Digvijay Singh Gehlot 

This is a very specific and unusual request, it looks like the approach you've taken of a fixed schedule of 90 days will mean you only get an email sent 4 times a year, and searching for tickets that only changed state to Open 90 days ago doesn't seem like a good approach; surely you should be telling the reporter that there hasn't been a change for more than 90 days!

I don't recommend that you use this approach, but the JQL that should give you what you want is:

project = TEST 
AND issuetype = Story 
AND status = Open 
AND status changed TO Open during (startofday(-90d), endofday(-90d))

[Digvijay Singh Gehlot]

Thank you @Stephen_Lugton for your message. It helped.

May you also guide me, what could be the JQL if the status has not be changed for more than 90 days?

[Stephen_Lugton]

@Digvijay Singh Gehlot 

The JQL that @Vishal Biyani suggested should do that for you:

project = TEST 
AND issuetype = Story 
AND status = open 
AND status changed to Open before startOfDay(-90d)

Also the original JQL that you posted in your question will do that

[Vishal Biyani]

@Digvijay Singh Gehlot 

Can you try with query

project = TEST and issuetype = Story AND status = Open AND status changed to Open before startOfDay(-90) AND NOT status changed after startOfDay(-90)

1. status = Open → Ensures the issue is currently in the "Open" status.
2. status changed to Open before startOfDay(-90) → Ensures the issue moved to "Open" at least 90 days ago
3. NOT status changed after startOfDay(-90) → Ensures no further status changes happened in the last 90 days.