• Community
  • Questions
  • How do I create a JQL query which uses dates but allows for non-working days?

How do I create a JQL query which uses dates but allows for non-working days?

I want to create an escalation service under script runner which automatically closes issues that have been in a certain status for 3 working days or more.

I'm struggling to create the query I need to identify the issues I want to close. The problem is that I want to identify issues which have been in the status I'm interested in for more than 3 working days - and ignore Saturdays and Sundays. I can't see that you can specify which day(s) an escalation service runs on so I need to run it with an interval of 1440 (daily) and allow for non working days in my query. I've looked at trying to use startOfWeek() and endOfWeek() relative to when the issue was last updated but I'm confusing myself with comlexity of what I'm coming up with given that the 'current week' is sometimes not the same 'week' the issue was last updated in.

Basically if an issue was last updated on a Monday or Tuesday then I want to auotmatically close it on the Thursday or Friday. This part I'm comfortable with. But if the issue was last updated on a Thursday then it needs to be closed on the following Tuesday (3 working days will have elapsed), and issues last updated on a Friday will need closing on the following Wednesday etc.

I'm happy to ignore holidays in the query (that would really get complicated) but is anyone able to help me with the query I need to use working days only in my query? If it is possible in anyway to obtain a 'day of week' from a Jira date or schedule an escalation service based on 'day of week' then I guess this would solve my issue but from what I can see this can't be done.

1 answer

0 vote

I appreciate the mention of the plugin, but really, this is a (good) question about how to query over working day ranges with JQL. You may get more eyes on this if you ask it as just a plain "jql" question.

AFAIK there is no easy way to do this with JQL (maybe not even a hard way). For a first port of call I would vote for https://jira.atlassian.com/browse/JRA-22506.

It may be possible to do something with https://jamieechlin.atlassian.net/wiki/display/GRV/Scripted+JQL+Functions#ScriptedJQLFunctions-dateCompare(Subquery,datecomparisonexpression) or https://jamieechlin.atlassian.net/wiki/display/GRV/Scripted+JQL+Functions#ScriptedJQLFunctions-expression(Subquery,expression), but don't have time at the moment to experiment. Will try to come back to this...

Suggest an answer

Log in or Join to answer
Community showcase
Louis De Jaeger
Posted yesterday in Off-topic

Friday fun: your best joke

Hi all Lets make this Friday fun really fun and post one (or more) of your best jokes! The joke can be about an Atlassian product, or just a really fun joke you want to share! I’m not the best j...

61 views 2 2
Join discussion

Atlassian User Groups

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

Find a group

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

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot