Jira aggregate worklogs only in specific dates range

Question

JIRA has an excellent ability to search issues with workLog items created in specific date and by a specific user. For example:

worklogDate > 2017-04-01 AND  worklogDate < 2017-05-01 AND worklogAuthor = some-user

In this search result I can see a column Time Spent - it's a total time spent on a task. How can I aggregate time from workLogs only for selected days? For example, we worked on the task in Mar and April. How to write JQL to calculate only April's time.

It is possible?

Answer 1

To get the time spent by the user by tasks, if you have access to the database, you can run this query:

select wl.timeworked, wl.worklogbody, wl.updateauthor, wl.updated,
    u.display_name, ji.summary, 
    concat(concat(p.pkey,'-'),ji.issuenum) as IssueKey 
from worklog wl
    inner join cwd_user u 
    on wl.updateauthor = u.user_name
        inner join jiraissue ji
    on ji.id = wl.issueid
        inner join project p
    on (ji.project = p.id)
            where issueid in (
                select j.ID
                from jiraissue j
                    inner join project p
                    on (j.project = p.id)
                        where u.user_name = 'userid')

Replace the userid with the userid of the person that submitted the worklog. Take note that for each JIRA ticket (issue), there can be multiple worklog submission by different user. This will give you every worklog submitted by the userid and it will also show you which ticket (issue) they are for. You can add in the date constraint in the where clause if you want to just query for specific timeframe. The unit of measurement for the timeworked column is in second as recorded by JIRA.