Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,366,500
Community Members
 
Community Events
168
Community Groups

Query to get transition tab time in source status

Edited

Hi,

I need to get the time difference between 2 status like in transitions tab of the issue and store it in the custom field.

CBC_Creative_Status time.PNG

Is there a way to get them through sql query from the DB?

Else anyother solutions?

Please help.

Thanks,

Krithica

3 answers

Hi,

For retrieving transitions tab, use the following query for ms sql server. Field time in source status also included.

With Transitions as
(select row_number() over (PARTITION BY JI.ID order by CG.CREATED asc) row#,JI.ID as ID,
CI.OLDSTRING as FromStatus, CI.NEWSTRING as ToStatus,
CG.CREATED as EXECUTED, JI.CREATED as CREATED
from testdb.dbo.jiraissue JI
join testdb.dbo.changegroup CG on CG.issueid = JI.ID
join testdb.dbo.changeitem CI on CI.groupid=CG.id and field = 'status'
LEFT JOIN testdb.dbo.nodeassociation X ON X.SOURCE_NODE_ID = ji.ID and X.ASSOCIATION_TYPE = 'IssueFixVersion'
LEFT JOIN testdb.dbo.projectversion Y ON Y.ID = X.SINK_NODE_ID
JOIN testdb.dbo.project ON project.ID = ji.PROJECT AND project.id = ?
JOIN testdb.dbo.issuestatus s ON ji.issuestatus = s.ID
where ji.issuetype=?)
select A.row#,A.ID,CAST(A.FromStatus AS NVARCHAR(MAX)) as FromStatus,CAST(A.ToStatus AS NVARCHAR(MAX)) as ToStatus,
datediff(SECOND, B.EXECUTED,A.EXECUTED) as timeinseconds,A.EXECUTED from Transitions A
join Transitions B on A.ID=B.ID and (B.row#=A.row#-1)
union
/* -- First Transition [Time In Source Status] --*/
select C.row#,C.ID,CAST(C.FromStatus AS NVARCHAR(MAX)) as FromStatus,CAST(C.ToStatus AS NVARCHAR(MAX)) as ToStatus,
datediff(SECOND, C.CREATED,C.EXECUTED) as timeinseconds,C.EXECUTED from Transitions C where C.row#=1
union
/* -- #Optional -- IssueCreation <to> First Transition Time In Source Status --*/
select D.row#,D.ID,CAST('Created' AS NVARCHAR(MAX)) as FromStatus,CAST(D.FromStatus AS NVARCHAR(MAX)) as ToStatus,
datediff(SECOND, D.CREATED,D.CREATED) as timeinseconds,D.EXECUTED from Transitions D where D.row#=1

Hi Arun,

 

I am trying to use the above SQL query provided by you but unable to get the desired result.

It gives me error on this line "where ji.issuetype=?)"

ScriptRunner performs this using scripted fields. 

scriptrunner_answer.PNGSetting as Duration will display weeks, days, hours, minutes and seconds. The 'inProgressName' is the status you want to time. 

0 votes
Andy Heinzer Atlassian Team Sep 15, 2017

It looks like there is a related thread where this might have already been answered.  I'd recommend checking out Get "time in source status" value using SQL

It appears that other users have been looking for this in the past and appear to have found both Oracle and MySQL syntax to find this information.

I believe that Transitions tab on the issue details is actually being provided by the JIRA suite utilities plugin.   In addition to that plugin you might be able to use a plugin like Time in Status to find the same information.

Suggest an answer

Log in or Sign up to answer
TAGS

Atlassian Community Events