Trying to determine a JQL where an issue was created 5 days prior to a person's "Start Date"

[Greg Chapman]

Hey, I am trying to create a JQL query where a user creates a ticket, for Onboarding.  However, the issue tracks a separate "Start Date".  And I am wanting to display all issues, that have a 5 day difference between the issue's "Created Date" and the actual "Start Date".  Can anyone help?  Thanks community!

[Niranjan · Accepted solution]

Hi @Greg Chapman ,

With script runner you can use a jql like

project=ABC and issueFunction in dateCompare("", "created = Start date+5d")

[Greg Chapman]

Thank you, we leverage "Power scripts for Jira" plug-in.  Would the syntax be similar?  (I'm fairly new to scripting).

[Jack Brickey · Accepted solution]

i think you can use Automation for this. Example of getting the difference between dates

{{issue.customfield_10015.diff(issue.customfield_10016).minutes}}

 Documentation - https://support.atlassian.com/jira-software-cloud/docs/use-smart-values-to-manipulate-and-format-dates/#calculating-the-difference-between-two-dates

[Fazila Ashraf]

@Jack Brickey  , this is nice.. Can this be used in a jql? 

Or should the value be stored on a field like a 'scripted field'?

[Jack Brickey]

do you mean a jql in a search or within the automation? TBH, i have not implemented so can't give you precise instructions. I do expect you can do this w/o needed a field to compare within automation. should be easy enough to try it out.

[Niranjan]

project=ABC and issueFunction in dateCompare("", "created = Start date+5d") can be used as a jql I suppose

[Greg Chapman]

@Jack Brickey we leverage "PowerScripts for Jira" and SIL manager as our tools for scripting.  This is my first time using this, as I am fairly new to Jira scripting.  I'm wondering how different the syntax is from what @Niranjan has provided.  (Thank you, btw.)

[Jack Brickey]

Quite different. Automation is built into Cloud and it does not require any scripting. Basically it is a lego like automation tool where you select various options and populate conditions, values, etc. if you give it a quick try I think you will find it intuitive. Project settings > Automation

[Fazila Ashraf · Accepted solution]

@Greg Chapman ,  It is not possible to compare 2 fields in JIRA by default.

Do you have scriptrunner? If yes, you can look at https://scriptrunner-docs.connect.adaptavist.com/jiracloud/enhanced-search.html#_datecompare 

[Greg Chapman]

We actually leverage "Powerscripts for Jira" as our tool to write custom scripts.  

[Alexey Matveev]

Hello @Greg Chapman ,

I believe that you should use a JQL like this:

key in silJQLExpression('startOfDay(created) < startOfDay(#{Start Date} - "5d")', 'project = YourProjectKey')

It would be nice if you had mentioned that you use Power Scripts in your question. In this case I would find your ticket faster.

Thank you!