JQL to find tickets created post 5PM and Before 8AM for entire month

Question

Hi All,

Is there a dynamic JQL to fetch tickets created post 5PM and before 8am the next day for an entire month?

Currently we are using

created >= "2024-11-12 17:00" AND created <= "2024-11-13 08:00" AND project = "ABCD" AND type = Task

and it only gives result for particular dates mentioned in JQL.

We need the data for an entire month. For example, Issues created on 1st Nov after 5pm and on 2nd Nov before 8 am. Then for 2nd Nov after 5pm and 3rd Nov before 8 am and so on....

Note - We are not using any plugins that provides additional JQL functions.

Dave Mathijs · Accepted Answer

Hi @Gaurav Arora Unfortunately, JQL doesn’t support recurring date functions, so you have to manually list each day. Alternatively, you could use a Jira automation rule or script to simplify and automate this if you need it regularly. (created >= "2024-10-01 17:00" AND created <= "2024-10-02 08:00") OR (created >= "2024-10-02 17:00" AND created <= "2024-10-03 08:00") OR (created >= "2024-10-03 17:00" AND created <= "2024-10-04 08:00") OR (created >= "2024-10-04 17:00" AND created <= "2024-10-05 08:00") OR ...

Bill Sheboy · Accepted Answer

Hi @Gaurav Arora

In addition to the suggestions from @Dave Mathijs ...

How often do you need to perform a query like this and how much money / effort are you willing to invest in it?

* Need this one time...* Export all of the issues for the month of interest, and use spreadsheet functions to extract the time from the created field for filtering

* Need this often...* Have money to spend on marketplace addons...* Investigate marketplace apps which extend the features of JQL or add enhanced reporting

* Do not have money to spend...* Assuming there are fewer than 100 issues, perform the query in an automation rule to load the Lookup Issues action, and then use smart value list filtering to find the ones matching the time criteria
* When there are more than 100 issues...Add a custom field for the "created time", use an automation rule to set the field for issues, and finally use the built-in JQL to filter on the field

Kind regards,
Bill