Search between hours of the day range

Question

Hey there

So, I have a field "planned at" and I want to create a filter that will search if someone have set a "planned at" date and time between 2:00 AM to 6:00 AM.

Now, startOfDay() doesn't help me much, because it searches only for the specific day the search was made for, while I want to know if there's something in the following days as well that is set for this hour range.

Any suggestions?

Payne · Answer

If you have the ScriptRunner add-on installed, you can use a scripted field to identify the hour component of the field and then query on it.

Gilad Peeri · Answer

Hey Petterson,

I'm trying to create a filter that will find a "planned at" field that was set for that time between 2:00 AM to 6:00 AM.

I would like to use this filter as a subscription that will run every hour, since I want to know in advance if someone have set a ticket to this time.

I'll try explain with an example:

Let's say somebody set a "planned at" field in a ticket to Feb 15th, 4:00 AM.

I'm looking for a filter that will find that to me, since it is within this hour range.

I've tried with the following filter, but it returns only tickets within this day, and I need for any day:

"Planned at" >= startOfDay("+2h") and "Planned at" <= startOfDay("+6h")

Petter Gonçalves · Answer

Hello Gilad,

Welcome to Atlassian community.

Are you trying to return issues where the date/time set in the "planned at" field is between 2:00 AM to 6:00 AM or you are trying to get issues where the "planned at" field was filled during that time?

In both situations, I think you will be able to retrieve the data you want using only JQL query, however, there are some external tools that can retrieve this information.

My recommendation would be to export your issues to a CSV file and use a spreadsheet software (Like Excel or Libre-office) to return the issues you want by using formulas.

If you would like to give you more context about how you are planning to use this filter, we could dig a little bit further on this to provide you with other options.

We will be looking forward to your reply.