Hi, I am trying to write a JQl query to show the following: * Shows the number of JIRA tickets closed by Assignee between 5pm and 9pm in January and February 2022 * I have this but it does not seem to be working as it should:* resolved >= "2022-01-01 17:00" AND resolved <= "2022-02-28 21:00" AND assignee in * I also need to show the number of tickets closed by Assignee on Saturdays and Sundays of January and February 2022* Not sure if JIRA caters for specific days? Any help is appreciated. Thank you.

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 time issue resolved

Drishti Maharaj

Hi,

I am trying to write a JQl query to show the following:

Shows the number of JIRA tickets closed by Assignee between 5pm and 9pm in January and February 2022
I have this but it does not seem to be working as it should:
- resolved >= "2022-01-01 17:00" AND resolved <= "2022-02-28 21:00" AND assignee in

I also need to show the number of tickets closed by Assignee on Saturdays and Sundays of January and February 2022
- Not sure if JIRA caters for specific days?

Any help is appreciated.

Thank you.

Accepted answers

Bill Sheboy

Hi @Drishti Maharaj

Short answer: you cannot perform that type of query with out-of-the-box JQL for Jira.

Longer answer:

JQL can help you query for a date range, or for a time range within a specific day, but not both at once.

This seems like a very specific query. If you need this just once, I recommend querying for your date range, exporting your data to another tool (e.g. spreadsheet) and then split the date/time values for the field to perform the filtering and reporting.

If you need this type of reporting often, you could try:

investigate marketplace addons which support such queries
investigate custom fields and automation rules to save the the time component for a field, allowing independent searches with built-in JQL

Kind regards,
Bill

All comments

AMIT GULATI

@Drishti Maharaj What error are you getting in the query? I am able to run it successfully

AMIT GULATI

@Drishti Maharaj Refer to this post for second part https://community.atlassian.com/t5/Jira-Software-questions/Tickets-createds-and-resolveds-in-weekend/qaq-p/1861801

Drishti Maharaj

Hi @AMIT GULATI ,

The query does run but the results are wrong - if I open the first one attached on the image, that was resolved "23/Feb/22 12:12 PM" and I am only looking for issues resolved between 17:00 and 21:00

AMIT GULATI

@Drishti Maharaj I got it now. The query is incorrect then. It is capturing all issues resolved start 1700hrs on 1st Jan and 2100hrs on 28th Feb.

You also need to add criteria to check that resolution time is only between 1700 and 2100hrs.

Drishti Maharaj

@AMIT GULATI yes but I am not sure how to actually check for that specifically for the time.

Rilwan Ahmed

Hi @Drishti Maharaj ,

I don't think there is any direct JQL to do your task.

Just a smal workaround, tyr this JQL to get tickets resolved between 17:00 PM and 21:00 PM for today.

project = XYZ AND resolved > startOfDay(17h) AND resolved < startOfDay(21h) ORDER BY resolved ASC

Bill Sheboy

Hi @Drishti Maharaj

Short answer: you cannot perform that type of query with out-of-the-box JQL for Jira.

Longer answer:

JQL can help you query for a date range, or for a time range within a specific day, but not both at once.

If you need this type of reporting often, you could try:

investigate marketplace addons which support such queries
investigate custom fields and automation rules to save the the time component for a field, allowing independent searches with built-in JQL

Kind regards,
Bill

Drishti Maharaj

Thank you @Bill Sheboy

Lauma Cīrule

Hi @Drishti Maharaj,

If you would be interested in a third-party app for reporting, you can check out eazyBI.

Here is an example of a report for Issues created outside working hours: https://community.eazybi.com/t/issues-created-out-of-office-hours/180. In a similar way, you would be able to build a report based on issue resolution time.

Let me know if you have further questions regarding eazyBI!
Lauma / support@eazybi.com

David Bonavia

You need to update your query to say:
((resolved >= "2022-01-01 17:00") AND (resolved <= "2022-02-28 21:00")) AND assignee in . . .

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

server

jira

jira-server

jql

query

resolution