How to get the time ticket was in a state

[Gopinath Velayudhan]

HI --

I am looking to get the information on the time that a ticket was in a particular state. Is there a way i can get this information from the Database or is there a Rest API that i can use to get this information. I found the following query in the web

https://confluence.atlassian.com/display/JIRACOM/Example+SQL+queries+for+JIRA#ExampleSQLqueriesforJIRA-Issues%27TimeinEachStatus%28NeedsFormattingLove%29

but this seems to be outdated and most of the tables referenced here are emtpy.

I user oracle database. If database is not the way to go can i use any REST or webservice call to get the information.

greatly appreciate your help

[Adam Jacobson · Accepted solution]

The tables referenced in that example query don't come up empty for me. The comment "::Key of project" is misleading through because it should actually be the issue key.

The expanded query that I used - based on that example - to get a sequence of statuses with their respective timestamps is:

SELECT
 pkey,
 changegroup.created,
 oldstring,
 newstring
FROM
 jiraissue,
 changeitem,
 changegroup
WHERE
 changegroup.id = changeitem.groupid AND
 changegroup.issueid = jiraissue.id AND
 jiraissue.pkey = '<issue key>' AND
 changeitem.field = 'status'
ORDER BY
 changegroup.created DESC

You could amend this to pick the valid statuses and/or do the calculation for you.

[Gopinath Velayudhan]

this does work for me as well. Thanks for the answer.