It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

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

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?

5 answers

3 votes
Payne Community Leader Aug 02, 2018

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

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 Community Leader Aug 02, 2018

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.

Like Monika Haecki likes this

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

Payne Community Leader Aug 02, 2018

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

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 Community Leader Aug 02, 2018

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 Community Leader Aug 02, 2018

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

type = Maintenance AND filter = "Due 2nd Wednesday"
Payne Community Leader Aug 03, 2018

@Martha Sapeta - 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 ...

Like Martha Sapeta likes this

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

0 votes
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

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?

0 votes
Ignacio Pulgar Community Leader Aug 02, 2018

I will pass these links on, thank you!

0 votes
Ignacio Pulgar Community Leader Aug 02, 2018

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

0 votes

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

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted in Jira

Demo Den Ep. 7: New Jira Cloud Reports

Learn how to use two new reports for next-gen projects in Jira Cloud:  Cumulative flow diagram and Sprint burndown chart. Ivan Teong, Product Manager, Jira Software, demos the Cumulative ...

314 views 1 3
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you