What JQL query can give me current week as Monday to Sunday, which works even on Sundays?

Lucky Uche January 23, 2022

I have a simple query which shows me all issues resolved in the current week starting from Monday: 

resolved > startOfWeek(1d)

This query works perfectly on every day of the week until we get to Sunday, which should be the last day of the week and should display everything resolved since the week started on Monday. Unfortunately, Sunday is fixed as the start of the week for Jira and it's not possible to change it (company-managed account, etc). 

Question is: is there any possible JQL query that could give me results with Monday to Sunday as the week, no matter what day of the week I'm checking it?

2 answers

2 votes
Pramodh M
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 23, 2022

Hi @Lucky Uche 

This JQL will give issue result from Monday to Sunday for the current week (no matter what day of the week you're checking it)

resolved >= startOfWeek("+1d") AND resolved <= endOfWeek("+1d")

This JQL will give issue result from Monday to Sunday for the previous week (no matter what day of the week you're checking it)

resolved >= startOfWeek("-6d") AND resolved <= endOfWeek("-6d")

Let me know if you need any clarification.

1 2 3 4 5 6 7 8 9 0

Thanks,
Pramodh

Lucky Uche January 23, 2022

Hello @Pramodh M  thanks for the response. Unfortunately, the JQL doesn't work on Sunday. It works fine from Monday to Saturday but on Sunday, like today, it doesn't.

Here's why: it considers today as "startOfWeek", so it would be looking for issues resolved from "startOfWeek("+1d")" which is tomorrow. I want it to show me issues resolved from Monday 17th to Today 23rd.

Pramodh M
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 23, 2022

Hi @Lucky Uche 

the JQL doesn't work on Sunday. It works fine from Monday to Saturday but on Sunday, like today, it doesn't.

The above statement is wrong. JQL searches for issues even on Sundays for startOfWeek function, infact startOfWeek() search starts on Sunday

Here's the Proof

sOW.png

Reference here

So when you search for issues, it should return the issues resolved as per your requirement!!

resolved >= startOfWeek("+1d") AND resolved <= endOfWeek("+1d")

+1d refers to Monday for startOfWeek and Sunday for endOfWeek in the above JQL

Go ahead and try it and see the result

Let me know if you have any queries

Thanks,
Pramodh

Lucky Uche January 23, 2022

Hello @Pramodh M 

I understand the JQL you sent but I don't think you understand the request I'm making, so let me try to elaborate.

In my company, today Sunday 23rd is the last day of the week, so I want a JQL that will give me issues resolved between Monday 17th and Sunday 23rd. The JQL you sent sees Sunday as start of the week and Monday as "start of the week +1d". This means that if I run this JQL today being Sunday, it will search for issues resolved between Monday 24th (which start of the week +1d) and Sunday 30th (which is end of the week +1d).

The difference is simply that while I am still in the week of 17th to 23rd, Jira is already in the week of 23rd to 29th. So startOfWeek("+1d") for Jira means Monday 24th and not Monday 17th which is what I actually want.

I hope this is clear.

Pramodh M
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 23, 2022

@Lucky Uche 

Got it. Thanks for the clarification.

Here you go this should work

resolved >= startOfWeek("-6d") AND resolved <= endOfWeek("-6d")

So if today is Sunday 23rd, it will go back 6 days which 17th Monday 

and end of the week which is on 29th - 6 days which is 23rd Sunday

So final result 17th Monday to 23rd Sunday right?

resolved >= startOfWeek("-6d") AND resolved <= endOfWeek("-6d")

Let me know if this doesn't return the result!!

Thanks,
Pramodh

Lucky Uche January 23, 2022

Thank you @Pramodh M for your help.

This JQL works perfectly today to give me the results of everything resolved from Monday 17th to Sunday 23rd, which is the current week for me.

The issue is that once I'm in a new week by tomorrow Monday 24th, then this JQL will be giving me data of a previous week rather than the current week. 

So, for any given week Monday to Sunday, the first JQL you sent with +1d works perfectly from Monday to Saturday giving me data from the current week, while this JQL with -6d works perfectly but only on Sunday to give me data from the current week.

What I'm looking for is a JQL that works perfectly from Monday to Sunday giving me data for the current week.

Pramodh M
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 23, 2022

That's why I gave two filters in the beginning @Lucky Uche 

This is for the current week

What I'm looking for is a JQL that works perfectly from Monday to Sunday giving me data for the current week.

This works no matter which day you run!! and same for the other as well

This one's from 24th Monday to 30th Sunday

resolved >= startOfWeek("+1d") AND resolved <= endOfWeek("+1d")

This is for the previous week

resolved >= startOfWeek("-6d") AND resolved <= endOfWeek("-6d")

Save the filters, since these are dynamic, running the JQL is enough to get the results!!

Unfortunately, you need two separate filters to manage the results!!

Thanks,
Pramodh

Lauren Ortiz
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
May 9, 2024

what about AND "Start Date" <= endOfWeek("+2d") AND "Start Date" >= startOfWeek("+1d")??

 

nm this is incorrect but I cant delete :/ 


 

1 vote
Jack Brickey
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 23, 2022

Have you considered enabling ISO8061 calendar? This is a global setting so be sure this is desirable across the board. changing the setting does require system admin permissions.

C9B61841-F1AD-4FF2-8845-D3BA045E2C4D.jpeg

Pramodh M
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
January 23, 2022

Good one @Jack Brickey Thanks

Lucky Uche January 23, 2022

I'm not sure of the details but I've raised the concern to the company system admin before and it can't be changed. This was the response I received:

This cannot be changed globally in Jira Cloud and is a known issue / limitation from Atlassian: https://jira.atlassian.com/browse/JRACLOUD-71611

That's why I'm seeking a JQL query that works even on Sunday. I want to believe the one I have is not the best possible.

Suggest an answer

Log in or Sign up to answer