...
ISSUESTATUSTRANSITIONS | |||||||||
|---|---|---|---|---|---|---|---|---|---|
Column | ISSUEID | ID | PERFORMER | PERFORMEDON | FROMSTATUSID | TOSTATUSID | INTERVAL | 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
| Code Block | ||||||
|---|---|---|---|---|---|---|
| ||||||
select
i.keykey(JQL.ISSUE) as "ISSUE",
ist.fromstatus as "FROM",
ist.tostatus as "TO",
ist.performer,
ist.performedon,
formatdurationnatural(ist.interval) as "Natural time",
from
formatduration(natural2duration(ist.interval), true) as "Business time"
from
issues i
inner join
JQL
inner join
issuestatustransitions ist on ist.issueid = i.idJQL.issueid
where
where iJQL.jqlquery =' issue = SSP-1 ' order by issue, performedon asc |
| ISSUE | FROM | TO | PERFORMER | PERFORMEDON | Natural timeBusiness time | |
|---|---|---|---|---|---|---|
| SSP-1 | In Progress | null | null | null | 14h 6m | 4h 46m |
| SSP-1 | To Do | In Progress | admin | 2018-01-18 20:45:52.661 | 5d 11h 26m2w 1d 1h 49m |
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.