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?

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

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

JQL Query for Tickets resolved less than 24 after their logged

Colin Young_4i89gv

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?

Accepted answers

Bill Sheboy

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

All comments

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

Solution available here :-

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

Colin Young_4i89gv

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

Bill Sheboy

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

Mehmet A _Bloompeak_

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.