JQL for Mon 05:30AM IST to Next Mon 05:30AM IST

[WasimBuden]

Dear Community,

I need to write a JQL that would give me the results of all the tickets created between Mon 05:30 AM IST till Next Mon 05:30 AM IST. 

For this I wrote -> created >= "2022-01-31 05:30" AND created <= "2022-02-07 05:30", but I don't want to write this every week, my profile timezone is set to +05:30GMT. Is there a way to get a  JQL that dynamically gets me data for this each week so I can use this JQL on a dashboard?

TIA,

Wasim

[Piotr Zadrożny _Eyzee_ · Accepted solution]

Hi @WasimBuden 

Try something like:

created >= startOfWeek(330m) AND created <= endOfWeek(330m)

You can read more here: https://support.atlassian.com/jira-software-cloud/docs/advanced-search-reference-jql-functions/#Advancedsearchingfunctionsreference-startOfWeekstartOfWeek--

[Piotr Zadrożny _Eyzee_]

Well I dont know if I understand correctly. If you need issues created after last Monday 5:30 till now, you just need: created >= startOfWeek(330m)

Best,

Piotr

[WasimBuden]

Hello @Piotr Zadrożny _Eyzee_ thank you for your response , what i really need is created >= startOfWeek(330m) AND created <= endOfWeek(330m) data for previous week.

So it should be created >= startOf(previous)Week(330m) AND created <= endOf(previous)Week(330m)

[Piotr Zadrożny _Eyzee_]

Well its not the prettiest way but you can write (you just can't mix time unit in those functions):

created >= startOfWeek(-9750m) AND created <= endOfWeek(-9750m)

where: 9750m is 7*24*60-5h30m (1 week minus 5h30m)

[WasimBuden]

Thank you @Piotr Zadrożny _Eyzee_ 

I am trying to get the same results for both of these JQLs, so it's Monday 5:30 AM to next Monday 5:30 AM, so I guess we need to change (-9750m) to align with Monday 05:30 AM as Jira counts Sunday as the start of the week?

[Piotr Zadrożny _Eyzee_]

Hi @WasimBuden 

Of course, you are right. From startOfWeek() official documentation: "this function considers Sunday to be the first day of the week".

So it should be:

created >= startOfWeek(-8310m) AND created <= endOfWeek(-8310m)

or

created >= startOfWeek(-8310m) AND created <= startOfWeek(1770m)

[Piotr Zadrożny _Eyzee_]

Hello @WasimBuden

Did the proposed solution help? If so, please mark it accepted to help other users find it.

Best,

Piotr

[WasimBuden]

Hello Piotr,

Unfortunately, it did not. I require to build a dynamic report/dashboard with data from last Monday to this Monday morning, so I am trying to set up a filter that gets me data from last Monday to this Monday, 5:30 AM to 5:30 AM.

For ex this week I am writing a manual filter created >="2022-02-07 05:30" and created =< "2022-02-14 05:30".

[Piotr Zadrożny _Eyzee_]

Hi @WasimBuden 

Have you checked the last proposed version? In my tests it works as intended ( "type in standardIssueTypes()" added in each version just to limit created issues below 1000+) :

• Static version:

• Dynamic version 1:

• Dynamic version 2:

Best regards,

Piotr

[WasimBuden]

Perfect!

It works exactly the way I wanted it too :-) Thank you for your time and patience in answering this Piotr appreciate it!

~Wasim

[Pramodh M]

Hi @WasimBuden 

Here's the JQL

created >= startOfWeek("+1d")

Thanks

[WasimBuden]

Hello Pramod

I would need data for tickets from last Monday 5.30 AM to this Monday 5.30 AM, not the current week!