List all issue status transitions from the database in Jira
Platform Notice: Data Center Only - This article only applies to Atlassian apps 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
This article provides SQL queries on how to get a list of all issue status transitions from the database in Jira, as well as issue status transitions for the past x days. A status transition can be seen at the "History" tab of an issue.
Solution
Query for all issue status transitions
SELECT changeitem.oldstring, changeitem.newstring, changegroup.author, changegroup.created FROM changeitem JOIN changegroup ON changeitem.groupid=changegroup.id WHERE changeitem.field='status';Example result
oldstring | newstring | author | created
--------------------+--------------------+----------+-----------------------
Open | Review | admin | 2015-05-25 09:27:46+08
Resolved | Closed | admin1 | 2015-05-25 09:51:30+08
Open | Closed | admin | 2015-05-25 09:59:22+08
Investigation | Analysis | admin2 | 2015-05-25 10:44:22+08
Analysis | Consultation | admin | 2015-05-25 10:51:13+08
Open | Resolved | admin | 2015-05-25 10:53:26+08
Resolved | Closed | admin1 | 2015-05-25 10:53:29+08
Change Approval | Change Development | admin | 2015-05-25 11:35:27+08
Open | Review | admin2 | 2015-05-25 12:05:58+08
Open | Resolved | admin | 2015-05-25 12:15:20+08
Resolved | Closed | admin | 2015-05-25 12:15:23+08
...Query for issue status transitions for the past x days
SELECT changeitem.oldstring, changeitem.newstring, changegroup.author, changegroup.created FROM changeitem JOIN changegroup ON changeitem.groupid=changegroup.id WHERE (changeitem.field='status' AND changegroup.created >= current_date - interval '7 days');Example result
oldstring | newstring | author | created
--------------------+--------------------+----------+-----------------------
Open | Review | admin | 2015-05-25 09:27:46+08
Resolved | Closed | admin1 | 2015-05-25 09:51:30+08
Open | Closed | admin | 2015-05-25 09:59:22+08
Investigation | Analysis | admin2 | 2015-05-25 10:44:22+08
Analysis | Consultation | admin | 2015-05-25 10:51:13+08
Open | Resolved | admin | 2015-05-25 10:53:26+08
Resolved | Closed | admin1 | 2015-05-25 10:53:29+08
Change Approval | Change Development | admin | 2015-05-25 11:35:27+08
Open | Review | admin2 | 2015-05-25 12:05:58+08
Open | Resolved | admin | 2015-05-25 12:15:20+08
Resolved | Closed | admin | 2015-05-25 12:15:23+08
...Issue status transitions for the past x days with issue key
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 the past 7 days.
Example result
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
...Updated on June 12, 2025
Was this helpful?
Still need help?
The Atlassian Community is here for you.