JQL Query for Tickets resolved less than 24 after their logged

[Colin Young]

I am trying to put together a query in JIRA that covers the following criteria:

• The ticket was logged last month - I know I can use endOfMonth(-1) and startOfMonth(-1) for this.
• Tickets that we closed within 24 hours of the created date.

I wondered if someone can point me in the right direction please?

[Bill Sheboy · Accepted solution]

Hi @Colin Young 

JQL is not a SQL, so you cannot compare different fields within a single query as you ask with out-of-the-box Jira: e.g. resolved within N time units of created.

To do that you would need a marketplace add-on for JQL to enhance the query or an improved reporting tool, or you could try these work-arounds:

• Brute force: export the data and compare the data in a spreadsheet
• Automation and Save the results: use an automation for Jira rule and a custom field to save the difference between resolved and created, and then query on that custom field
• Automation and JIT: use a scheduled automation rule to compare things resolved in the last N time units, and report on the differences between created and resolved.  If you know a threshold value of interest, this could be a smaller result set.  For example, issues that took longer than 24 hours to resolve.

Best regards,

Bill

[Bloompeak Support]

Hi @Colin Young ,

As an alternative, you can try 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.

[Vikrant Yadav]

Hi @Colin Young Welcome to Community!

You can try something like this :- 

project = "JIRA Support" AND issuetype = "New Project Request" AND status changed to closed BEFORE "2020/12/01" AFTER "2020/11/30 00:00"

Thanks

Vikrant Yadav

[Vikrant Yadav]

Solution available here :- 

https://community.atlassian.com/t5/Jira-questions/JQL-for-tickets-closed-within-a-time-range/qaq-p/348531

[Colin Young]

Thank you Vikrant.

I think I am looking for something more dynamic like

project = "JIRA Support" AND issuetype = "New Project Request" AND status changed to closed BEFORE created+1d

It doesn't like using created after BEFORE. 

[Vikrant Yadav]

If you don't want to use BEFORE AFTER use DURING

status changed to "closed" during ("2020-06-03", "2020-06-07")

[Colin Young]

Thanks again Vikrant.

I think I need to avoid using static dates. The main idea is to get tickets that were closed last month and within 24 hours of them being created.

I can create the query that shows all tickets from current month -1. I just can't get the closed within a day part of it :(

[Colin Young]

Tried this but JIRA aint happy

status changed to resolved during (created, created\u002b1d)