How can I determine if today is monday?

[Cash Coyne]

I want to have a JQL that will basically do this now() = StartofWeek()

And use it like this:

(now() = StartofWeek() and commentedOn >-3d) or and (now() != StartofWeek() and commentedOn >-1d))

With the goal of retrieving issues commented on yesterday, or if today is Monday, retrieve issues commented on Friday.

But it appears that I can't use the now function like that.

How can I do this?

[Cash Coyne · Accepted solution]

I was trying to get issues with an update date from the prior day, but that wouldn't work for Mondays.  However, I added this to my JQL and that seems to have done the trick:

 (updated < -3d AND updated > startofWeek() OR updated < -5d)

[Andrew Laden]

The nested and with or functionality should work as a defacto if. Thats a standard programming trick. 

So the question is how to get the current day into something that can be used in the left side of the comparison, since you cant use the function there. You need it in a field value. So...

Try creating a calculated custom field where the value is based on the day of the week. Even if it is not on screen it should be calculated. I don't have script runner so I cant tell you how to do that calculation, but is should be easy. Worst case, you can create some automation to update that field in every open issue every midnight.

You could then use that in your comparison. if the field was called "CurrentDay" and calculated to the 3 letter abbreviation

(CurrentDay = "mon" AND lastCommentedDate >= startOfWeek(1) AND lastCommentedDate < startOfWeek(2)) OR (CurrentDay != "mon" AND lastCommentedDate >= startOfDay(-1) AND lastCommentedDate < startOfDay())

[Ste Wright]

Hi @Cash Coyne 

It looks like you have Scriptrunner, based on the commentedOn function (which isn't native to Jira) - is that correct? Or potentially another App which extends JQL functions?

If this is Scriptrunner, I envisage it would be better to use lastCommentedDate - i.e

• Monday: lastCommentedDate >= startOfWeek(1)
• Before Weekend: lastCommentedDate <= startOfWeek(-2)

^ I haven't tested these, but as far as I can tell, the logic is correct.

Just to note, startOfWeek() is classed as Sunday, with Saturday being the endOfWeek(). See more information on this help page.

Ste

[Cash Coyne]

Unfortunately, I tried different configurations of this and am still not able to get ONLY Friday's comments to show up only on Monday and the rest of the days of the week that the query is run to get only the prior day's comments to show up.  

It will work for Monday, when specifying startofweek, but on other days, I get the prior day's AND Friday's comments

Still need it to give me Friday's comments on Monday and the prior day's comments on all other days.  Only want to see one day's worth of comments.

[Ste Wright]

Hi @Cash Coyne 

You can refine the queries above to specifiy a specific day, for example...

1. Comments on Monday: lastCommentedDate >= startOfWeek(1) AND lastCommentedDate < startOfWeek(2)
2. Comments on Friday: lastCommentedDate >= startOfWeek(-2) AND lastCommentedDate < startOfWeek(-1)
3. Prior Day: lastCommentedDate >= startOfDay(-1) AND lastCommentedDate < startOfDay()

These use a second lastCommentedDate query to specify the day - so for example, (2)'s logic is now that the comment was made after or equal to Friday, but not Saturday (and consequentially, not Thursday).

Ste

[Cash Coyne]

@Ste Wright , that all makes sense.  But my problem is that I want to use this part of the filter when someone runs the filter on Monday.

 lastCommentedDate >= startOfWeek(1) AND lastCommentedDate < startOfWeek(2)

Every other day of the week, I want to use this part of the filter:

lastCommentedDate >= startOfDay(-1) AND lastCommentedDate < startOfDay()

The problem is, I can't do something like:

(now() = "Monday" and ( lastCommentedDate >= startOfWeek(1) AND lastCommentedDate < startOfWeek(2)) or (now() != "Monday" and (lastCommentedDate >= startOfDay(-1) AND lastCommentedDate < startOfDay()))

It's the now() = "Monday" part that I'm having toruble with.

[Ste Wright]

Hi @Cash Coyne 

I don't know if that's possible without some customisation.

I assume you're effectively trying to use IF statements, so the results would "differ" depending on the day of the week it's run on?

If yes, JQL isn't SQL - in that instance you'd need multiple filters, and to run the relevant one each day.

Edit: Added an option, see next comment.

Ste

[Ste Wright]

Hi @Cash Coyne 

After reviewing Andrew's answer, I did revisit using an additional piece of metadata to do this.

You can do this using Automation - you just have to be cognisant of execution limits if this is a Global/multi-Project rule, depending on your Plan level.

If this is for one Project, I would run this as a Project-level rule, as these executions are not counted towards your monthly allowance.

---

Automation Rule

You need to identify what today's day of the week is in a field on every relevant Issue.

Prerequisite:

• Create a custom Short Text field to house this information
• This field does not have to be on the Screen
• In the rule below, I've used the field name "Today"

Your Rule will look something like this:

• Trigger: Scheduled 
  • Run Rule every = 1 days
  • At = 12:00 AM
  • Run a JQL search = check to TRUE
  • JQL = <JQL Here> - to locate relevant Issues, eg. all Issues, Issues with Comments, etc
  • Only include issues that have changed since the last time this rule executed = check to FALSE
• Action: Edit Issue
  • Today = {{now.dayofWeekName}}

---

Notes

• In the Trigger, the JQL clarifies which Issues to run the rule against - you could make this all Issues in your Project, or limit it based on specific parameters, eg. Issue Type, Status, numberOfComments, etc)
• The Action uses a Smart Value to set the format of the text - to see more formatting options for dates/times, see this page

---

Result

You should then be able to use this field in your JQL, something along these lines:

Today ~ "Monday" AND lastCommentedDate >= startOfWeek(-2) AND lastCommentedDate < startOfWeek (-1) OR Today !~ "Monday" AND lastCommentedDate >= startOfDay(-1) AND lastCommentedDate < startOfDay()

---

I'm sure it's also possible using an App like Scriptrunner, and creating a Scripted Field, if that is your preference - then referencing that in the JQL instead.

Let us know if this works for you!

Ste

[Cash Coyne]

Thanks Ste,  Actually that's the road I've started down and may end up using that. 

[Ste Wright]

Awesome! Let us know how you get on :)

Ste