ISSUE CHANGES | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
Column | ISSUEID | ID | USERNAME | FIELD | CREATED | FROMVALUE | TOVALUE | INTERVAL | FROMSTRING | TOSTRING |
Type | BIGINT | BIGINT | VARCHAR | VARCHAR | TIMESTAMP | VARCHAR | VARCHAR | BIGINT | VARCHAR | VARCHAR |
Indexed | x | x | x |
It allows to access to the issue changes history for the issue fitracked by JIRA. You have to know the name used by JIRA to track the attribute. Unfortunately those field names are documented nowhere as they belong to the private JIRA Java API. I.e: for the issue status attribute, the field name is: 'status'. You have to figure out out the name.
Example: how to get the time that each issue remained (interval) in a particular state
Getting the changed fields for any issue is something really trivial. So we have to cover a much more complex issue that users might face in the real World: the time between two consecutive changes, named INTERVAL by the Driver.
Please don't be overwhelmed due the complexity of this issue. Simply, we wanted to support the special INTERVAL column. This is a very particular use case supported by the Driver.
JIRA does not log data for issue creation as changes, so issues that did not modify the field after its creation are not included in the ISSUECHANGES table as well as the interval time in the current status is not shown either.
In the 2.5.0 version a new column was supported: INTERVAL.
A new table is supported for Issue Status Transitions since the 6.1.0 version. You should use it to get the interval that each issue remained in a particular state.
Example:
Explore the status changes in my reported issues during last week
select KEY(JQL.ISSUE) AS "Issue", ic.field AS "Field", ic.fromvalue, ic.tovalue, ic.fromstring, ic.tostring, formatdurationnatural(interval) as "Interval (Natural time)" from JQL join issuechanges ic on ic.issueid = JQL.issueid where JQL.QUERY = ' reporter = currentUser() and updated < -7d ' and ic.field='status' order by "Issue", "Field", ic.created
Issue | Field | FROMVALUE | TOVALUE | FROMSTRING | TOSTRING | Interval (Natural time) |
---|---|---|---|---|---|---|
DEMO-11 | status | 10000 | 3 | To Do | In Progress | 0m |
DEMO-12 | status | 10000 | 3 | To Do | In Progress | 0m |
DEMO-15 | status | 10000 | 3 | To Do | In Progress | 2d 4h 44m |
DEMO-15 | status | 3 | 10001 | In Progress | Done | 2d 9h 52m |
DEMO-16 | status | 10000 | 3 | To Do | In Progress | 3w 0d 0h 48m |
DEMO-16 | status | 3 | 10001 | In Progress | Done | 1d 1h 48m |
DEMO-17 | status | 10000 | 3 | To Do | In Progress | 10h 44m |
DEMO-17 | status | 3 | 10001 | In Progress | Done | 3d 5h 52m |
DEMO-18 | status | 10000 | 10001 | To Do | Done | 20h 30m |
DEMO-19 | status | 10000 | 10001 | To Do | Done | 3d 20h 24m |
DEMO-20 | status | 10000 | 10001 | To Do | Done | 5d 8h 59m |
DEMO-21 | status | 10000 | 10001 | To Do | Done | 1w 0d 15h 37m |
DEMO-22 | status | 10000 | 10001 | To Do | Done | 1w 3d 6h 57m |
DEMO-23 | status | 10000 | 10001 | To Do | Done | 1w 5d 8h 35m |
The INTERVAL column is calculated column by the plugin, so it is not directly fetched from the JIRA Java API. It returns the time in milliseconds and natural time (24 * 7) that the issue remained between the fromvalue and tovalue. The issue changes must be ordered by the field and the created column since the plugin calculates the interval by comparing the ordered sequence of records only