Need help with specifying a dynamic period of time on search filter

[Henry Heppe-Smith]

I want to search for Issues created in the last year but only during the hours of: 17:00pm - 06:00am

Is there a way to create a dynamic search, which only shows issues during certain hours of a day, whilst still searching for multiple days?

This is what i came up with until now, this sadly doesn't work and only shoes issues for the current day:

created >= startOfYear() AND created <= endOfYear() AND created > startOfDay("-7h") and createdDate < startOfDay("+6h")AND issuetype in ("Purchase request", Global, "Service request", Incident, "Access request") ORDER BY created DESC

[Trudy Claspill · Accepted solution]

Hello @Henry Heppe-Smith 

Welcome to the Atlassian community.

It is not possible to do that with native JQL and just the built-in Created timestamp field.

You would need to have the time of issue creation tracked in a separate field so that you could apply the criteria to just the time portion without regard for the date portion. You could use an Automation rule to populate your custom Creation Time field as each issue is created.

The startOfDay function is creating a date/time for comparison based on the start of the current date adjusted by the parameter you provide. That function can't be used to set a time range that will applied to ever date/time in the Created timestamp.

It might be possible to create the JQL with the addition of a third party app that extends JQL capabilities, or that can be used for more complex reporting. I have not researched to find an app with that specific capability.

[Henry Heppe-Smith]

Hello @Trudy Claspill

thank you for the quick reply regarding my issue.

I will take a look for an app that might have those capabilities. 

Will also check if creating an automation is an option for us. Have you ever built an automation, creating a custom creation time field, on existing tickets?

[Trudy Claspill]

There is not a native custom field type specifically for Time. You would need to create a custom text field.

You would use the Automation rule to parse the Created date/timestamp to pull out the time portion and then put it into the custom text field.

I have created an automation to parse the time out of the Created date/timestamp.

I would encourage you to spend some time trying to work it out as a learning experience, and post your questions in the community when you need help. One helpful resource is the documentation on smart values for date and time and the functions that you can use with fields that hold date/timestamps.

https://support.atlassian.com/cloud-automation/docs/jira-smart-values-date-and-time/

[Henry Heppe-Smith]

Thank you!

I will try to figure it out.