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

Gaurav Arora
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 13, 2024

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.

2 answers

2 accepted

3 votes
Answer accepted
Dave Mathijs
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 13, 2024

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

 

Gaurav Arora
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 14, 2024

Hi @Dave Mathijs ,

Thanks for confirming this! I suspected that JQL doesn't support recurring date functions, but I needed someone to verify it. I appreciate your input on this.

Like # people like this
2 votes
Answer accepted
Bill Sheboy
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
November 13, 2024

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

Gaurav Arora
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
November 14, 2024

Hi @Bill Sheboy ,

Thanks for your input!

We want to avoid any additional costs, so we'll try your approach of using lookup and filtering smart values, along with simple (though unfortunately long) JQL as suggested by Dave.

Like Bill Sheboy likes this

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
PREMIUM
TAGS
AUG Leaders

Atlassian Community Events