Track time spent in each status using SQL

[Mukama]

Has anyone downloaded the Jira logs and written a query to track time spent in each status for example; 

1. From "To - Do" to  "In - Progress"

And also filtered it by timezone, and day.

If yes kindly walk me through how to do it.

[Zoryana Bohutska _SaaSJet_]

Hi @Mukama 

You can consider other apps from Atlassian Marketplace. For example, Time in status for jira cloud tracks time spent in each status automatically.

Regards

[Mehmet A _Bloompeak_]

Hi @Mukama ,

As an alternative, you can try Status Time Free app. It displays status duration for you in issue detail page as below.

[KAGITHALA BABU ANVESH]

Hello @Mukama ,

To export the data from Jira DB is so difficult. me trying to get articles. But unable to get them on Status wise time spent reports.

You need to combine Users Table , Workflow , Worklog  and Users related info. It's highly difficult taks.

select p.pkey,j.issuenum,w.author,w.CREATED, (w.timeworked/60/60) as "Log work"
from project p
inner join jiraissue j on p.id=j.project
inner join worklog w on w.issueid=j.id order by w.CREATED DESC;

The above will gives total time spent by a user in project. this is from Atlassian only I think.

My suggestion is to look for Plugins.

Thanks