JQL query (or other way) to create filters with issue relative dates

Hi,

I have a request to create reports based on the time an issue remains in a specific state.

The request is the following:

* List of issues that took longer than 30 minutes to assign (both by

week and by day) (new issues remain unassigned by default)

* Percentage of issues that took longer that 30 minutes to assign (both

by week and by day)

Since operators 'was' and 'changed' operate only against string values or date offsets from the current date, I can't use them.

What I'm thinking is to create a custom scripted field that will return the difference of createdDate-dateAssigned and use in in JQL to check the idle time (30mins) against it.

This question pops up now, how can I get the issue history from the API? Could you direct me please?

Thanks in advance.

8 answers

1 accepted

Ok, managed to get what I wanted.

Here it is, the groovy scripted field:

/*
 * Calculate the time in minutes an issue remained unassigned.
 * Since scripted fields are updated only when the issue is updated, the initial value
 * of the custom field will be -1.
*/
import com.atlassian.jira.ComponentManager

def componentManager = ComponentManager.getInstance()
def changeHistoryManager = componentManager.getChangeHistoryManager()
def eventTime = -1

def assignee = issue.assignee

if (assignee) {
    //eventTime= changeHistoryManager.getChangeItemsForField(issue, "assignee").find {it.toString}?.getCreated().getTime()
    changeHistoryManager.getChangeItemsForField(issue, "assignee").find {
        eventTime = it.getCreated().getTime()
    }
    return ((eventTime - issue.getCreated().getTime())/60000).toBigInteger().toDouble()
} else {
    return ((new Date().getTime() - issue.getCreated().getTime())/60000).toBigInteger().toDouble()
}

Thank you @Jamie

Cool. FWIW I agree with your points about external dependencies and hitting the db directly...

1 vote

It's easy enough with a scripted field. Check the creation date, easy.

If the assignee is set on create then the time difference is zero.

If the assignee is set, then get the assigned datetime:

def componentManager = ComponentManager.getInstance()
def changeHistoryManager = componentManager.getChangeHistoryManager()
changeHistoryManager.getChangeItemsForField(issue, "assignee").find {it.toString}?.getCreated()

If the assignee is not set then the time difference is... either null, or now - creation time. Which leads you to a problem, in that calculated fields only get updated when the issue changes.

So you might be looking at a custom report, a new jql function, or modifying the "time to first response" report/gadget.

In short, not that easy after all.

I was thinking of you actually, trying to create this field. Thanks for the tips - great help - I'll dig into it and try to set the field based on its creation conditions.

Couldn't I have a condition in the script to store, lets say a -1 value to issues with no assignee? I could then exclude those created 30 mins before from the JQL filter.

sounds good.

If you have access to the database through SQL, it would be easier to get the results you want. Would that type of solution work for you?

Unfortunatelly not. Although I do have access to the DB and the query is more or less easy to create, this will be my last resort as it comes along with other kinds of effort (schedule auto execution, format the results send an email, maintenance blabla...) - OVERKILL!

Actually, your points raised are generally easier to do and maintain with database tools than with Jira customizations. Also you already paid for your database tools if you are using a non open source database. There are also open source reporting tools as well.

Here is an url about current Jira reporting capabilities and integrations that might assist you.

https://confluence.atlassian.com/display/ATLAS/Reporting+in+JIRA

Thanks Norman, your tips are welcome.

I'm using MySQL but I'm not a big fan of solving an issue by creating external dependencies. If it can't be solved on the spot, then it won't be solved at all!

I am to until I figured out the queries management wanted was easier to do by setting up a website and have their reports generated hourly and daily to html and pdf formats all through database tools. Less than a days work. Now, I am only asked about adding new reports (rarely now) and I saved a few licenses since some managers did not need Jira accounts. I also did not need to "customize" Jira just for reporting reasons.

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Published yesterday in Jira Software

How large do you think Jira Software can grow?

Hi Atlassian Community! My name is Shana, and I’m on the Jira Software team. One of the many reasons this Community exists is to connect you to others on similar product journeys or with comparabl...

165 views 4 8
Read article

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