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

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

ISSUE	FROM	TO	PERFORMER	PERFORMEDON	Natural time

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.