How can I create a JQL filter with time range between two time frames in the same day

[Marcello Valastro]

Hi there,

I would like to run a filter for a calendrer year that filters out a particular time frame in JSM. 

This is something dynamic and does not specify the exact date.

For example, I want to filter out tickets that are between 7:00 and 16:00 for morning tickets and from 16:00 to 11:30 for evening tickets. This is used for reporting purposes.

I am not sure JQL can support this.

Any suggestions for this please?

Unfortunately I cannot know the Jira version as I am not an admin and plug-ins are not related to this project.

Thanks.

[Tim Kopperud]

Edited: I removed erroneous answer. 

TimK.

[Mazen Chmait_FusionTech Innovations_Marketplace Partner]

Hello @Marcello Valastro ,

If my understanding is correct you are looking to get tickets created between time of day.
If my understanding is correct you can use the following JQL createdDate >= startOfDay(7h) and createdDate <= startOfDay(16h) to get the tickets between 7:00 and 16:00, for the other part to get the tickets from 16:00 to 11:30 it is not clear. Do you mean 11:30 in the morning next day?

Regards,
Mazen

[Marcello Valastro]

Hi @Mazen Chmait_FusionTech Innovations_Marketplace Partner, 

Thanks for reaching out to me and for sending me this. 

Yes that is correct, I am trying to get tickets created between time of day. It seems to work, but I would require to have this for a calendrer year/month and not just only the current day, as I this would determine, what certain type of issues/priority levels are during the year/month, and not just the day.

I notice that created >= startOfmonth(7h) and created <= startOfmonth(16h) does the same but for only the first day of the month, to get the tickets between 7:00 and 16:00.

For the second part, what I meant was to get tickets from 16:00 to 23:30 for evening tickets. Also I was looking for night tickets, so to get tickets from 23:00 to 7:30 in the morning.

Not sure if it is clearer like that.

Regards,

Marcello

[Kristine Sube]

Thanks it did work in my Usecase! 
Best Regards,
Kristine

[Amna Khan]

hi @Marcello Valastro 

i know its a late post but i was able to get the exact same result as you are looking using the app Reports, Charts & Graphs (our org has the license) and it gave me the data like this 

[Joseph Chung Yin]

@Marcello Valastro -

Welcome to the community.  To supplement what @Mazen Chmait_FusionTech Innovations_Marketplace Partner suggested, you can also try the following JQL -

created >= "2023/12/01 08:00" and created <= "2023/12/01 12:00"

The above JQL will give all issues created on 12/01 from 8am to 12 noon.

The valid date with timestamp syntax are - 'yyyy/MM/dd HH:mm', 'yyyy-MM-dd HH:mm'

Hope this also helps.

Best, Joseph Chung Yin

Jira/JSM Functional Lead, Global Technology Applications Team

Viasat Inc.

[Marcello Valastro]

Hi @Joseph Chung Yin , 

Thanks for your added assistance and JQL query. 

Originally, I tried out created >= "2023-11-01 07:00" AND created <= "2023-11-28 16:00"

Then I  adjusted it to created >= "2023-06-01 07:00" AND created <= "2023-12-30 16:00". it brought out the tickets created during that timeframe, but when amending the JQL from 07:00 to 11:00, the tickets under that timeframe where still showing.

I was under the impression that the syntax can be just HH:mm, but it is invalid. 

Regards,

Marcello

[Joseph Chung Yin]

@Marcello Valastro -

What do you mean by "tickets under that timeframe where still showing"?  Are you stating that issues created before "2023-06-01 11:00" are still being returned?

Please give us your exact JQL used.  Lastly, you cannot just use HH:mm only when querying against issue created date.

Best, Joseph

[Marcello Valastro]

Hi @Joseph Chung Yin apologies from my end,

When using the JQL query created >= "2023-06-01 07:00" AND created <= "2023-12-30 16:00" , it was still returning tickets that were created after 16:00. This particular ticket created was 2023-11-30 at 21:31.

This is the JQL query that I used.

I see, it is not a valid format and has to be 'yyyy/MM/dd HH:mm', 'yyyy-MM-dd HH:mm', 'yyyy/MM/dd', 'yyyy-MM-dd'.

The JQL I would like to use would be for a whole month, to return created tickets between 07:00 to 16:00. Your JQL query returns for the day, but can it be amended to return a whole month?

Kind regards,

Marcello

[Joseph Chung Yin]

@Marcello Valastro -

Unfortunately, out of the box it doesn't provide you with the custom JQL function to give you all the issues created between 07:00 to 16:00 for a given month as the timestamp portion is focus on the "Day" syntax.

I believe you will need to take a look at third party add-on to provide you with what you are asking for in Atlassian Marketplace - https://marketplace.atlassian.com/search?query=JQL

Hope this still helps.

Best, Joseph