How to List all the Issue Status Transitions for Past x Days with Issue Key from Database in JIRA

Still need help?

The Atlassian Community is here for you.

Ask the community

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
...

Last modified on Feb 26, 2016

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.