How to List all the Issue Status Transitions for Past x Days with Issue Key from Database in JIRA
Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.
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
The purpose of this article is to show how to get a list of all the issue status transition from database in JIRA. The status transition can be seen at the "History" tab of the issue. This article will help on getting a list of issue status transition of all issues in past x days with issue key in JIRA by executing a SQL command against the JIRA database. This SQL query is an extension of the SQL query from How to List all the Issue Status Transitions for Past x Days from Database in JIRA.
Workaround
- Log in to JIRA database.
- Run the following SQL command to list the status transitions:
SELECT project.pkey, jiraissue.issuenum, changeitem.oldstring, changeitem.newstring, changegroup.author, changegroup.created FROM changeitem JOIN changegroup ON changeitem.groupid=changegroup.id JOIN jiraissue ON changegroup.issueid=jiraissue.id JOIN project ON jiraissue.project=project.id WHERE (changeitem.field='status' AND changegroup.created >= current_date - interval '7 days');Sample Query for issue key FPS-4:
SELECT project.pkey, jiraissue.issuenum, changeitem.oldstring, changeitem.newstring, changegroup.author, changegroup.created
FROM changeitem JOIN changegroup ON changeitem.groupid=changegroup.id
JOIN jiraissue ON changegroup.issueid=jiraissue.id JOIN project ON jiraissue.project=project.id
WHERE (project.pkey= 'FPS' and jiraissue.issuenum = '4' and changeitem.field='status' and changegroup.created >= current_date - interval '7 days');The query will return records for past 7 days.
- Example result will look like following:
pkey | issuenum | oldstring | newstring | author | created
------+----------+-------------------+--------------------+----------+----------------------------
TEST | 5 |Open | Review | admin | 2015-05-25 09:27:46+08
TEST | 4 |Resolved | Closed | admin1 | 2015-05-25 09:51:30+08
TEST | 4 |Open | Closed | admin | 2015-05-25 09:59:22+08
TEST | 4 |Investigation | Analysis | admin2 | 2015-05-25 10:44:22+08
TEST | 4 |Analysis | Consultation | admin | 2015-05-25 10:51:13+08
TEST | 4 |Open | Resolved | admin | 2015-05-25 10:53:26+08
TEST | 3 |Resolved | Closed | admin1 | 2015-05-25 10:53:29+08
TEST | 1 |Change Approval | Change Development | admin | 2015-05-25 11:35:27+08
TEST | 4 |Open | Review | admin2 | 2015-05-25 12:05:58+08
TEST | 3 |Open | Resolved | admin | 2015-05-25 12:15:20+08
TEST | 2 |Resolved | Closed | admin | 2015-05-25 12:15:23+08
...