How to List all the Issue Status Transitions for Past x Days 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 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 from Database in JIRA

Workaround

  • Log in to JIRA database.
  • Run the following SQL command to list the status transitions:
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');

The query will return records for past 7 days.

  • Example result will look like following:
     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
...

Last modified on Feb 26, 2016

Was this helpful?

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