How to calculate average time in status
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
In its standard configuration, Jira does not support the generation of reports on the average time an issue remains in a particular status through the user interface.
Solution
Reporting time in status for Jira Issues from the Database:
This SQL query is designed to provide extensive information about issue status changes within each project over the past week. It focuses on issues that are not archived and provides an average time in status for the entire project.
For Posgressql:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SELECT
p.pname AS "Project Name",
CONCAT(p.pname, '-', jiraIssue.id) AS "Issue Number",
ci.oldstring AS "From Status",
historyStep.start_date AS "Status Start",
ci.newstring AS "Target Status",
historyStep.finish_date AS "Status End",
historyStep.caller AS "User",
CONCAT(EXTRACT(DAY FROM (historyStep.finish_date - historyStep.start_date))::TEXT, ' days ',
EXTRACT(HOUR FROM (historyStep.finish_date - historyStep.start_date))::TEXT, ' hours ',
EXTRACT(MINUTE FROM (historyStep.finish_date - historyStep.start_date))::TEXT, ' minutes') AS "Time spent in status",
CONCAT((AVG(EXTRACT(EPOCH FROM (historyStep.finish_date - historyStep.start_date))) OVER (PARTITION BY p.pname) / (24 * 60 * 60))::INTEGER::TEXT, ' days ',
((AVG(EXTRACT(EPOCH FROM (historyStep.finish_date - historyStep.start_date))) OVER (PARTITION BY p.pname) / (60 * 60))::INTEGER % 24)::TEXT, ' hours ',
((AVG(EXTRACT(EPOCH FROM (historyStep.finish_date - historyStep.start_date))) OVER (PARTITION BY p.pname) / 60)::INTEGER % 60)::TEXT, ' minutes') AS "Project Average Time in Status"
FROM
os_historystep AS historyStep
INNER JOIN
jiraissue AS jiraIssue ON jiraIssue.workflow_id = historyStep.entry_id
JOIN
project p ON jiraIssue.project = p.id
JOIN
changegroup cg ON jiraIssue.id = cg.issueid
JOIN
changeitem ci ON cg.id = ci.groupid
WHERE
jiraIssue.updated >= NOW() - INTERVAL '7 days'
AND
jiraissue.archived = 'N'
AND
ci.field = 'status'
ORDER BY
"Project Name",
"Issue Number",
"Status Start";For MS SQL server:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
SELECT
p.PNAME AS [Project Name],
CONCAT(p.PNAME, '-', JIRAISSUE.ID) AS [Issue Number],
ci.OLDSTRING AS [From Status],
HISTORYSTEP.START_DATE AS [Status Start],
ci.NEWSTRING AS [Target Status],
HISTORYSTEP.FINISH_DATE AS [Status End],
HISTORYSTEP.CALLER AS [User],
CONCAT(DATEDIFF(DAY, HISTORYSTEP.START_DATE, HISTORYSTEP.FINISH_DATE), ' days ',
DATEDIFF(HOUR, HISTORYSTEP.START_DATE, HISTORYSTEP.FINISH_DATE) % 24, ' hours ',
DATEDIFF(MINUTE, HISTORYSTEP.START_DATE, HISTORYSTEP.FINISH_DATE) % 60, ' minutes') AS [Time spent in status],
CONCAT((AVG(DATEDIFF(SECOND, HISTORYSTEP.START_DATE, HISTORYSTEP.FINISH_DATE)) OVER (PARTITION BY p.PNAME) / (24 * 60 * 60)), ' days ',
(AVG(DATEDIFF(SECOND, HISTORYSTEP.START_DATE, HISTORYSTEP.FINISH_DATE)) OVER (PARTITION BY p.PNAME) / (60 * 60)) % 24, ' hours ',
(AVG(DATEDIFF(SECOND, HISTORYSTEP.START_DATE, HISTORYSTEP.FINISH_DATE)) OVER (PARTITION BY p.PNAME) / 60) % 60, ' minutes') AS [Project average Time in Status]
FROM
jiraschema.OS_HISTORYSTEP AS HISTORYSTEP
INNER JOIN
jiraschema.JIRAISSUE AS JIRAISSUE ON JIRAISSUE.WORKFLOW_ID = HISTORYSTEP.ENTRY_ID
JOIN
jiraschema.PROJECT P ON JIRAISSUE.PROJECT = P.ID
JOIN
jiraschema.CHANGEGROUP CG ON JIRAISSUE.ID = CG.ISSUEID
JOIN
jiraschema.CHANGEITEM CI ON CG.ID = CI.GROUPID
WHERE
JIRAISSUE.UPDATED >= DATEADD(DAY, -7, GETDATE())
AND
JIRAISSUE.ARCHIVED = 'N'
AND
CI.FIELD = 'status'
ORDER BY
[Project Name],
[Issue Number],
[Status Start];Replace jiraschema with your actual Jira database schema.
Note:The reporting timeframe of 7 days was intentionally added to minimize the impact on the database. If this timeframe is extended, consider mitigating the impact by running the query for one project at a time.
Was this helpful?