Issue Status Transitions
From the 7.14.0 version INTERVAL has become a reserved word in H2 and cannot be used to create columns with that name.
So the INTERVAL column has been renamed to INTERVALTIME.
ISSUESTATUSTRANSITIONS | |||||||||
---|---|---|---|---|---|---|---|---|---|
Column | ISSUEID | ID | PERFORMER | PERFORMEDON | FROMSTATUSID | TOSTATUSID | INTERVALTIMEÂ | FROMSTATUS | TOSTATUS |
Type | BIGINT | BIGINT | VARCHAR | TIMESTAMP | BIGINT | BIGINT | BIGINT | VARCHAR | VARCHAR |
Indexed | x |
It gets all the time (in natural units) that an issue remained in a particular state (interval) and its transition-related data.
It is one of the few tables that are not a straightforward representation of the Jira API. It has been created due the users' interest in reporting issue transitions
select key(JQL.ISSUE) as "ISSUE", ist.fromstatus as "FROM", ist.tostatus as "TO", ist.performer, ist.performedon, formatdurationnatural(ist.interval) as "Natural time" from JQL inner join issuestatustransitions ist on ist.issueid = JQL.issueid where JQL.query =' issue = SSP-1 ' order by issue, performedon asc
ISSUE | FROM | TO | PERFORMER | PERFORMEDON | Natural time |
---|---|---|---|---|---|
SSP-1 | In Progress | null | null | null | 14h 6m |
SSP-1 | To Do | In Progress | admin | 2018-01-18 20:45:52.661 | 5d 11h 26m |
In the example above, the SPP-1 issue remained in the To Do status for 5d 11h 26m (natural time units) and was transitioned by the admin user on the 2018-01-18 date to the In Progress status.
Then the issue remained in the In Progress status (current status) for 14 hours 6 minutes at the time to perform the SQL query. Note that the performer user, performed on date and to status are null as the issue is not transitioned yet.