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

Example
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
ISSUEFROMTOPERFORMERPERFORMEDONNatural time
SSP-1In Progressnullnullnull14h 6m
SSP-1To DoIn Progressadmin2018-01-18 20:45:52.6615d 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.