Time in Source Status JQL

[Theresa Tran]

Without using plugins, what is the best way to pull the time in each status?

I'm trying to pull the time from (Open to In Progress) and (In Progress to Ready for Testing) in the TRANSITION tab. Unfortunately, I cannot figure out how to use JQL to pull the total of these numbers (in a handful of defects). 

[Mesut Yilmazyildirim]

Is this what you are looking for;

select 
 p.pkey||'-' ||i.issuenum as jira_id,
 s.pname last_status,
 row_number() over(partition by st.issueid order by st.groupid) status_order,
 st.status S_from,
 st.new_status S_to, 
 nvl(st.date_start,i.created) as start_time, 
 st.date_end as stop_time,
 round (st.date_end - nvl(st.date_start,i.created),3) stop_start
from jirasd.jiraissue i,
 jirasd.project p,
 jirasd.issuestatus s,
 (select cg.issueid, cg.id as groupid, to_char(ci.newstring) new_status, to_char(ci.oldstring) status,
 LAG(cg.created) OVER (partition by cg.issueid ORDER BY cg.id) date_start, 
 LAG(cg.id) OVER (partition by cg.issueid ORDER BY cg.created) prev_groupid,
 cg.created date_end
 from jirasd.changeitem ci,
 jirasd.changegroup cg
 where ci.field = 'status'
 and ci.groupid = cg.id
 ) st
where p.id = i.project
and i.issuestatus = s.id
and st.issueid = i.id

[Cameron Pierce]

Thank you, the solution was very helpful.