How can I query tickets which was created after past Thursday?

Alex Li_9kx2mj

Our oncall week starts from Thursday to next Wednesday.

I want to build a filter for search all tickets that crated during these 7 days. 

The oncall week is across 2 weeks. startOfWeek may not work as expected.

project = "Oncall" AND created >= startOfWeek(4d)

This will work in Thursday, Friday Saturday of the week-1, but it may not work for Sunday, Monday, Tuesday, Wednesday of the week-2.

                      On-call week
                 +-----------------------+
                 |                       |
SUN MON TUE WED THU FRI SAT SUN MON TUE WED THU FRI SAT
                 |       |   |           |
                 +---+---+   +-----+-----+
                     |             | in these days we need to use:
                     |             +-----> created >= startOfWeek(-3d)
                     |
                     | in these days we need to use:
                     +--> created >= startOfWeek(4d)

 

How can I combine them to one single JQL, something like this

(created >= startOfWeek( 4d) AND now().dayOfWeek() in (THU, FRI, SAT)) OR (created >= startOfWeek(-3d) AND now().dayOfWeek() NOT IN (THU, FRI, SAT))

But now().dayOfWeek() doesn't work in JQL.

Alex Li_9kx2mj

I finally cracked it.

My actually use case is that the oncall started from Tuesday. (I used Thursday as start day in the question for easy to describe).

created >= startOfWeek(2d)

This will work well, when the time is in first week(Tuesday ~ Saturday).

But it will select nothing when the time enters the 2nd week(Sunday and Monday) because `startOfWeek(2d)` means current week's Tuesday which is in the future.

Suppose it is Sunday now. (Apparently it is 2nd week's Sunday), how can I match the JIRA of last Tuesday, Wednesday, Thursday, Friday and Saturday?

(created >= startOfWeek(-5d) and created < startOfWeek(-4d) 

matches last Tuesday. 

created >= startOfDay(-5d) and created < startOfDay(-4d))

matches the day before 5 days 

So the following criteria would match (the last Tuesday which is before 5 days)

(created >= startOfWeek(-5d) and created < startOfWeek(-4d) and created >= startOfDay(-5d) and created < startOfDay(-4d))

Only on Sunday it could match JIRAs. It won't match anything on any other day, for the day before 5 days wouldn't be Tuesday on any other days.

By this way, we can build criteria which would only valid on Sunday:

1. matches last Tuesday which is the day before 5 days
2. matches last Wednesday which is the day before 4 days
3. matches last Thursday which is the day before 3 days
4. matches last Friday which is the day before 2 days
5. matches last Saturday which is the day before 1 days

Also we can build criteria which would only valid on Monday:

1. matches last Tuesday which is the day before 6 days
2. matches last Wednesday which is the day before 5 days
3. matches last Thursday which is the day before 4 days
4. matches last Friday which is the day before 3 days
5. matches last Saturday which is the day before 2 days
6. matches this week's Sunday which is the day before 1 days

Here is my solution -

(created >= startOfWeek(2d) OR
created >= startOfDay() OR
(created >= startOfWeek(-5d) and created < startOfWeek(-4d) and created >= startOfDay(-5d) and created < startOfDay(-4d)) OR 
(created >= startOfWeek(-4d) and created < startOfWeek(-3d) and created >= startOfDay(-4d) and created < startOfDay(-3d)) OR 
(created >= startOfWeek(-3d) and created < startOfWeek(-2d) and created >= startOfDay(-3d) and created < startOfDay(-2d)) OR 
(created >= startOfWeek(-2d) and created < startOfWeek(-1d) and created >= startOfDay(-2d) and created < startOfDay(-1d)) OR 
(created >= startOfWeek(-1d) and created < startOfWeek() and created >= startOfDay(-1d) and created < startOfDay()) OR 
(created >= startOfWeek(-5d) and created < startOfWeek(-4d) and created >= startOfDay(-6d) and created < startOfDay(-5d)) OR 
(created >= startOfWeek(-4d) and created < startOfWeek(-3d) and created >= startOfDay(-5d) and created < startOfDay(-4d)) OR 
(created >= startOfWeek(-3d) and created < startOfWeek(-2d) and created >= startOfDay(-4d) and created < startOfDay(-3d)) OR 
(created >= startOfWeek(-2d) and created < startOfWeek(-1d) and created >= startOfDay(-3d) and created < startOfDay(-2d)) OR 
(created >= startOfWeek(-1d) and created < startOfWeek() and created >= startOfDay(-2d) and created < startOfDay(-1d)) OR 
(created >= startOfWeek() and created < startOfWeek(1d) and created >= startOfDay(-1d) and created < startOfDay()))