Helpful Jira Query for Total Time Spent

My last two posts have detailed information about a major project that I had an opportunity to lead. As part of that project, I wrote a fairly detailed retrospective where I wanted to cover interesting facts, issues that I encountered, and the resolutions to those issues.

One of the interesting facts that I included was the total number of hours I logged against the project in our tracking software, Jira. What I learned very quickly was that Jira does not have very extensive time reporting out of the box. Most time reporting appears to require third-party add-ons. Some of those add-ons are free while others are paid. I have absolutely no idea what my company does and doesn’t have. My only time in the tool is to create my Epics, Stories, Tasks, etc. and to log work against them.

It seems crazy to me that something so basic is not readily available in the tool. (If I’m overlooking something point me in the right direction, please!) At the very least, I feel like the work done in the sub-parts of the Epic should roll up to the Epic itself. The worst part is that it doesn’t seem to be just me searching for this information. A not-so-exhaustive search identified a feature request for exactly this type of function to be added. That feature request was quite old and well-looked after by members of the community.

I’ll admit – for all intents and purposes, I’m a Jira rookie. After a little trial and error and some Google-fu, I was able to put together a query that displays what I’m looking for! It doesn’t produce a sexy report, but it seems to be worth having in my back pocket until I can figure out exactly how to obtain said sexy report. There were a lot of people looking for similar information, so I hope this is helpful. This query returns time worked in a little pop-up directly below the search bar.

For me, the following code only shows me the Stories linked to Epics, but no Tasks or Sub-tasks. The total time displayed appears to be correct, at least.

[code language=”text”]project = [Project Name] AND “Epic Link” = [EPIC-###] AND (issuetype = Story OR issuetype = Task OR issuetype = Sub-task) AND issueFunction in aggregateExpression(“Total time”, “timespent.sum()”) [/code]

Note: You’ll want to replace the name of your project and however you number your Epics (the stuff in brackets). If you happen to find that copy/paste doesn’t work, try to re-type all the quotation marks. In my ample experience (ha!), this resolves the broken query.

Later on, I found that there was a duplicate story with some work logged against it. To query multiple Epics you can change the code a little. Drop this after the first AND in the query above and include as many Epics as you need (separating them with a comma).

[code language=”text”]”Epic Link” IN [EPIC-123], [EPIC-234][/code]

What I hope to do now is to compare the time spent in various phases of the project and the number of issues seen on those phases. I’m hoping to draw some type of correlation between the amount of time spent testing that particular phase and the number of issues seen when actually performing the tasks.

If you have a better way of reporting Jira time information, please share with me here or reach out to me on Twitter!


Leave a Reply