Making specific searches for time?

[Tiffany Duncan]

Hi,

We need to estimate how many hours we answered by assignee tickets between 5 PM PST and 9 am PST. What would be the best way to search that and divide it by month between May 2017 and Present? 

Would be ideal to generate a report?

[Burak Kocak]

If you have jira and excel but you access to jira db;

First you should search the issues in jira 

project = <your project> And created >= startofmonth(-17) and resolution is empty

than add column created and resolved to you search. After getting you issues as excel (1000 row limit !) or csv you can Use =month(A1) for months and =TIME(HOUR(A1),MINUTE(A1), SECOND(A1)) a1 is the cell no

so you can filter night shift issues. 

Also you can find difference resolved -created for how many hours spend for answering the ticket. 

If you have access to db,

select * from jiraissue j left join project p on p.id = j.project where p.pkey =‘<project key>’ and j.created >= to_date(2017-05-01,’yyyy-mm-dd’) 

And use same functions in excel .