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.
Is there a way to get them through sql query from the DB?
Else anyother solutions?
Please help.
Thanks,
Krithica
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.
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 CREATEDfrom testdb.dbo.jiraissue JIjoin testdb.dbo.changegroup CG on CG.issueid = JI.IDjoin 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_IDJOIN testdb.dbo.project ON project.ID = ji.PROJECT AND project.id = ?JOIN testdb.dbo.issuestatus s ON ji.issuestatus = s.IDwhere 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 Ajoin 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#=1union/* -- #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.
Setting as Duration will display weeks, days, hours, minutes and seconds. The 'inProgressName' is the status you want to time.
It looks like you're new here. Sign in or register to get started.
