Need some help with syntax in a filter

Question

I am looking to show all tickets opened after 6pm and before 6am (after hours) for the past three years. Right now, this is the syntax I have but I am not quite there:

project = "Service Desk" and issuetype in (Incident, "Service Request") and created >= "2020/01/01" AND created >= startOfDay("-6h") AND created <= startOfDay("+16h") order by created DESC

I appreciate the help.

Vamsi Kandala · Answer

Hi @Mikel Whipple

You can try this:

project = "Service Desk" and issuetype in (Incident, "Service Request") and created >= "2020/01/01 06:00" AND created <= endOfDay("-6h") order by created DESC

This will fetch all the tickets created on or after Jan 1st 2020 6:00 AM to today 6:00 PM.

Hope this helps.

Thanks,
Vamsi

Alex Koxaras -Relational- · Answer

Hi @Mikel Whipple

There isn't a way to do that with native jira functionalities. The startOfDay will yield issues which has to do with the today date, whichever this is, so the JQL that you have will not yield correct results. My advice is to:

* export all issues in an excel and then filter out the data you need
* get an app with can filter issues based on time

There is this post https://community.atlassian.com/t5/Jira-questions/Query-cases-created-in-certain-time-period-for-each-day/qaq-p/1406506 which might be able to help you if you want to do things manually within Jira.