Forums

Articles
Create
cancel
Showing results for 
Search instead for 
Did you mean: 

Search between hours of the day range

Gilad Peeri February 8, 2019

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?

2 answers

0 votes
Payne
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.
February 11, 2019

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.

0 votes
Petter Gonçalves
Atlassian Team
Atlassian Team members are employees working across the company in a wide variety of roles.
February 8, 2019

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.

Gilad Peeri February 9, 2019

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") 

Suggest an answer

Log in or Sign up to answer