How to add a time constraint to a status change in JQL?

Question

I want to specify in my query to make sure I'm only retrieving tickets that were changed from one status to another within 7 days. So, in my example, if the status of the ticket was "Closed" then 6 days later it changed to "Customer Response", I want that ticket to be included:

Assignee in (LIST OF ASSIGNEES)
and createdDate >= '2022-03-01' and createdDate <= '2022-03-31'
and
(
status changed from "Completed" to "Closed"
and status changed from "Closed" to "Customer Response"
and status changed to "Customer Response" XXXXX
)
or
(
status changed from "Cancelled" to "Closed"
and status changed from "Closed" to "Customer Response"
and status changed to "Customer Response" XXXXX
)

Where I have written XXXXX I assume I need some sort of logic, but I'm not sure how it would be written. Any help would be appreciated.

Josh Martin · Answer

Hi @Alex

I have done a bit of research and I don't think we can accomplish what you're looking for.  It does not appear that there are dates associated with the statuses; only dates for the issue itself.

Alex_4jtkro · Answer

Hi Josh - this looks good but instead of referring to today's date I need to be looking at the date of the status's. So for example, I need it to show tickets that were moved in to the Customer Response status from the Closed status with 7 days of them being in the Closed status.

So if we're looking at a ticket which was Closed on the 1st of March which moved in to Customer Response on the 3rd of March, I need it to be pulling those tickets back. I think your solution is looking at todays date instead of referring to the dates of the status's.

Sorry this is a bit tricky to explain

Josh Martin · Answer

Hi @Alex ,

You can do that by utilizing DURING like so:

Assignee in (LIST OF ASSIGNEES)
and createdDate >= '2022-03-01' and createdDate <= '2022-03-31'
and
(
status changed from "Completed" to "Closed"
and status changed from "Closed" to "Customer Response"
and status changed to "Customer Response" DURING (startOfDay(-6), startOfDay())
)
or
(
status changed from "Cancelled" to "Closed"
and status changed from "Closed" to "Customer Response"
and status changed to "Customer Response" DURING (startOfDay(-6), startOfDay())
)

Utilizing DURING requires you use a start and end date. In the above example, I used startOfDay(-6) as the start date, meaning the start date will be the beginning of 6 days ago. I used startOfDay() as the end date, meaning the end date will be the beginning of today.