How to query for specific days of the week in a given month?

[Martha]

We have a number of recurring maintenance windows every month which are specified by the week number and the day of the week (ex. second Wednesday every month). I need to be able to write a query to show me all the tickets due the second wednesday of every month and the second sunday of every month. I can sort of specify the second week of the month using startOfMonth +/- and filtering for the 8th to the 14th, but I can't see a way to get it to only show me tickets that are due on a Wednesday. It seems that startOfWeek and endOfWeek only works to specify a day of the week for the past 7 days.

Is there not a way to get a list of all tickets that are due this month on a Wednesday?

[Payne · Accepted solution]

startOfWeek(3d), startOfWeek(10d), startOfWeek(17d), startOfWeek(-4d) are all Wednesdays, so something like:

duedate = startOfWeek(-25d) OR duedate = startOfWeek(-18d) OR duedate = startOfWeek(-11d) OR duedate = startOfWeek(-4d) OR duedate = startOfWeek(3d) OR duedate = startOfWeek(10d) OR duedate = startOfWeek(17d) OR duedate = startOfWeek(24d)

should identify all Wednesdays from today back to the beginning of the month as well as forward to the end of the month (plus a bit into the previous month or forward to the next month, depending upon which week you're in). If you then combine that with your idea of limiting to the 8th through the 14th utilizing startOfMonth(), something like the following may be what you need:

duedate >= startOfMonth("+7d") AND duedate <= startOfMonth("+13d")  AND (duedate = startOfWeek(-25d) OR duedate = startOfWeek(-18d) OR duedate = startOfWeek(-11d) OR duedate = startOfWeek(-4d) OR duedate = startOfWeek(3d) OR duedate = startOfWeek(10d) OR duedate = startOfWeek(17d) OR duedate = startOfWeek(24d) ) 

[Martha]

That's basically what I have now, but the issue is that I can't separate out the tickets due the same week, but on different days (and therefore on different maintenance windows).

[Payne]

The example I provide will list only issues due on the 2nd Wednesday; you could have a second filter, a variation of this one, that would list issues due on the 2nd Sunday, your other maintenance day.

[Martha]

Omg that's amazing! But I need to adjust the formula slightly for each month, yes?

[Payne]

No, as soon as you hit the 1st day of a new month, the filters should then list issues for the new month, thanks to the duedate >= startOfMonth("+7d") AND duedate <= startOfMonth("+13d") portion of the JQL. The numbers in the startOfWeek() calls (-4, 3, 10, etc.) will always work, as those are offsets in relation to Sunday (day 0).

[Martha]

This is fantastic! I've set up queries using duedate and I can get all the specific days I want! However, if I use our custom Maintenance field, which has a date and time, it looks like startOfMonth works fine, but startOfWeek does not. Grrr so close! I will keep at.

[Ignacio Pulgar]

You both almost have it!

Now that you've got a method for getting issues due on Wednesdays, as well as a range of days of month where the a second weekday can occur, you just need to combine both queries in an appropriate manner:

1. Build a filter which retrieves issues due on a Wednesday with as many startOfWeek() functions as you feel convenient for you. As it's going to be a long query, I recommend using the short field name 'due' instead of 'duedate', as well as removing unnecessary whitespaces. Save that filter and name it in example 'Due on a Wednesday'.
2. Now build the query for retrieving all issues due within the range of occurrence of a second weekday. Save the filter with name 'Due 2nd weekday'.
3. Now, build a third filter with this jql: filter = "Due on a Wednesday" AND filter = "Due 2nd weekday". Save it with name 'Due 2nd Wednesday'.

And you got it! :)

[Ignacio Pulgar]

Finally, you may use your combined filter in any other queries like this:

type = Maintenance AND filter = "Due 2nd Wednesday"

[Payne]

@Martha - Glad you're getting close! Due Date is a date field (no time component), so checking for equality (e.g. duedate = startOfWeek(3d)) works. For a datetime field, though, it needs to be expanded a bit like this: maintenance >= startOfWeek(3d) and maintenance <  startofWeek(4d). So, you'll want to replace each startOfWeek() check with a pair like that. i.e.

duedate >= startOfMonth("+7d") AND duedate <= startOfMonth("+13d") AND ( (maintenance >= startOfWeek(-25d) and maintenance < startOfWeek(-24d) ) OR (maintenance >= startOfWeek(-18d) and maintenance < startOfWeek(-17d) ) OR ...

[Martha]

Ah ok, well glad there is at least a workaround! Thanks very much, I've got all four queries working now! :D :D :D

[AKBAR]

@Ignacio Pulgar @Payne  @Martha 

Guys, Please help me with the JQL for retrieving issues created on Saturday and Sunday for each week.

Unable to retrieve those issues I have to use this filter for Dashboard to track the newly created tickets on Saturday and Sunday for every week...

[Martha]

@AKBAR Can you confirm, you need a query to show you all issues created on Saturday and Sunday for the previous week? If so, the query would look something like this:

(project=yourTeamName AND createdDate=startOfWeek(-6)) AND (project=yourTeamName AND createdDate=startOfWeek()) AND createdDate <=1w

startOfWeek(-6) is Saturday and startOfWeek() is Sunday. To specify only tickets created on Saturday or Sunday in the past week, you just add createdDate with a range of less than 1 week.

[AKBAR]

@MarthaThe above query isn't returning me the results.

Instead I tried as below;

" project = ABC and (Created >= Saturday Date1 and Created <= Monday Date1) OR (created >= Saturday Date2 and created <= Monday Date2) "

Saturday date1 is the Saturday of the first week and Saturday Date2 is the Saturday of the second week. and vice versa for Monday also.

I have to change the dates of Saturdays and Mondays every month...

Thanks.

[Martha]

@AKBAR what are results do you get from the query? (The one using startOfWeek(-6) and startOfWeek().)

I should say that to show all tickets created on Saturday/Sunday for the past month, see the very first response in this thread. To cover the entire month, you need something like:

To get all Sundays in the month: CreatedDate = startOfWeek() OR createdDate = startOfWeek(7) OR createdDate = startOfWeek (21) OR createdDate = startOfWeek(28)

Then just include the same for all Saturdays, so include startOfWeek(-6), startOfWeek(6), startOfWeek(12), startOfWeek(24), startOfWeek(30)

[Ignacio Pulgar]

Someone with coding skills might find inspiration by having a look at these sources:

https://www.google.es/amp/s/mraddon.blog/2018/02/02/some-jira-groovy-date-calendar-script-tricks/amp/

https://github.com/adamstraube/JiraJavaGroovyScripts/blob/master/Day-of-week.groovy

Maybe a custom field set by a Script Listener listening for Issue Edited events can do the job:

https://scriptrunner.adaptavist.com/latest/jira/listeners.html

Or maybe a Scripted Field:

https://scriptrunner.adaptavist.com/latest/jira/scripted-fields.html

[Martha]

I will pass these links on, thank you!

[АНО ДПО ДА]

I make script field in adaptavist scriptrunner:

import com.atlassian.core.util.DateUtils
import com.atlassian.jira.datetime.LocalDate
import com.atlassian.jira.component.ComponentAccessor
import com.atlassian.jira.issue.RendererManager

if (!issue.getResolutionDate()) return -1
def timestamp = issue.getResolutionDate()
def cal = Calendar.getInstance()
cal.setTime(new Date(timestamp.getTime()))
double weekday = cal.get(Calendar.DAY_OF_WEEK) - 1;
if (!weekday) return 7
return weekday

 Return:

1 - Monday
2 - Tuesday
...
7 - Sunday
-1 - issue not resolved

[Ignacio Pulgar]

The Script Listener approach would require to listen to Issue Created events, as well.

[Ignacio Pulgar]

You may do so by hardcoding dates manually into your query (either absolute or relative).

Do you count with ScriptRunner? If that's the case, we can start thinking of a solution.
However, there's a procedural workaround here which would let you get the issues you need with a query:

Create a new issuetype named ie: Maintenance.
Use the new issue type just for creating those Maintenance tasks.
(Optional) Make Due Date required.

After that, you'll be able to get all those tasks with a simple query like:
type = Maintenance

[Martha]

Thanks for the response! We've basically done the setup you suggested: we already had an issue type for Maintenance, and we've also added a Maintenance Close Window that is a specific date, so I can search for those (we still have Due Date as well). The problem is that it will take some time to update all the tickets, as well as getting people to put in the maintenance tickets correctly in the future, so I was hoping for some sort of work around in the meantime. Additionally, this setup allows me to display all the maintenance tickets for a given time frame, but I still can't display only the tickets for the next maintenance window, unless I hard-code the date (which then has to be updated every month).

However, I'm told we do have ScriptRunner, though I've never used it before. Would I be able to create a script to search for things like tickets due every second Wednesday of the month?