Filter tickets by any created on weekends and after 5.30pm

[Mihir Ruparelia]

Hello, 

I wanted to ask how to create a filter that will show all tickets created in Jira after 5.30pm and on weekends. 

I did find a thread which said ``` createdDate >= startOfWeek(-1d) and ( (createdDate >= startOfDay() + 17h or createdDate < startOfDay() + 8h ```

But I'm missing something, can anyone help?

I also need it for a specific Jira project

[Giovanni Melai · Accepted solution]

Hi @Mihir Ruparelia

I played around with this just because it was funny. Anyway, I don't think there is a general solution for what you are trying to do if I got it right. 

The main issue is that you need to refer to specific hours in different time frames and you can't really do it. So you cannot create a JQL that fits all the cases in the past, but if we agree on reducing the scope to the actual week there is something we can do. 

Starting from your question, if I get it right you want to check which are the issues created outside the working hours for all days of the week plus the weekend. I assume the SOD is at 9 am and the EOD is at 5:30 pm.

In this case, we can use startOfWeek() as the static reference and build the query around it. I double-checked and startOfWeek() is 12 am on Sunday (I think this is based on the "Use ISO8601 standard in Date Picker" configuration, but by default, it's not enabled in Jira). 

To do the math I started counting from 5:30 pm on the previous Friday (when the weekend started).

For some, I needed to convert in minutes as @Jack Brickey did, because of the half-hour

(created >= startOfWeek("-1830m") AND created < startOfWeek("33h")) OR
(created >= startOfWeek("2490m") AND created < startOfWeek("57h")) OR
(created >= startOfWeek("3930m") AND created < startOfWeek("81h")) OR
(created >= startOfWeek("5370m") AND created < startOfWeek("105h")) OR
(created >= startOfWeek("6819m") AND created < startOfWeek("129h")) OR
(created >= startOfWeek("8250m") AND created < startOfWeek("153h"))

So if you use it now that is Friday, you will see all the issues created outside the working hours from last Friday. On Monday it will reset for next week (actually, it will reset on Sunday at 12 am but I think you'll use it on Monday) 

I hope this helps :) 

[Mihir Ruparelia]

@Giovanni Melai  Sorry for the late reply, just saw this today, I will give it a go and come back to you.

[Mihir Ruparelia]

Thank you for this, worked well with yours and Jack's answer. 

[Jack Brickey · Accepted solution]

Hi @Mihir Ruparelia ,

Let's assume you are looking for the following...

find all issues created after 5:30 PM on Friday and before 8:00 AM on Monday

in this case try the following. You can tweak the JQL to suit your exact needs. You would run this Filter each Monday morning by setting up a subscription.

created > startofday(-4350m) and created < startofday(8h)

The -4350m Is -4350 minutes. If my math is right that should equate to the number of minutes between 5:30 PM on Friday to 8 AM on Monday. Please do check my math. Note, you have to use minutes here since you can't use a decimal in the JQL statement such as -72.5h 

please let me know if this makes sense and if you need any further assistance.

[Mihir Ruparelia]

@Jack Brickey Thanks for this, sorry for the late reply I didn't get any notifications I will take a look. 

[Mihir Ruparelia]

This is perfect thank you Jack. I used the template and was able to create the filter :)