How to query date for which time was logged in JIRA DB?

Julianna Barta December 1, 2021

The query I've been using right now looks like this:

SELECT
i.issuenum,
substr(i.summary,30) as COMPONENT,
cg.ID,
cg.issueid,
au.lower_user_name,
cg.AUTHOR,
to_date(SUBSTR(cg.created,1,10)) as CREATEDATE,
ci.FIELD,
ci.OLDVALUE,
ci.NEWVALUE,
(to_char(ci.Newvalue)-(NVL(to_char(ci.oldvalue),0)))/3600 AS LOGGEDHOUR


FROM alm_jira_repos.changegroup cg

INNER JOIN alm_jira_repos.jiraissue i on cg.issueid = i.id
INNER JOIN alm_jira_repos.changeitem ci on ci.groupid = cg.id AND ci.FIELD='timespent'
INNER JOIN alm_jira_repos.app_user au on cg.author = au.user_key

The total of logged hours is OK, but I would need to split them into weeks. The problem is that cg.created field shows the date when the timelogging was made, but not the date, onto which time was logged (hope it makes any sense). 

Could you please help, where could I find the needed field in JIRA DB?

 

0 answers

Suggest an answer

Log in or Sign up to answer