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.

[Bill Sheboy · Accepted solution]

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

[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

[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

[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

[AMIT GULATI]

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

[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.