We have talked about the Servlet extraction of worklogs:

We have talked also about the Tempo API REST worklog extraction using groovy:

Now its time to know how to extract the Jira worklogs using SQL queries, here the query:


select
w.ID as "Worklog Id",
w.issueid as "Issue Id",
concat(p.pkey,"-",i.issuenum) as "Issue Key",
i.summary as "Issue Summary",
round(w.timeworked/3600,2) as "Hours",
w.startdate as "Work Date",
u.user_name as "User Name",
u.display_name as "User Full Name",
p.pkey as "Project Key",
p.pname as "Project Name",
ty.pname as "Issue Type Name"
from worklog w
inner join jiraissue i on
i.id=w.issueid
inner join cwd_user u on
u.user_name=w.author
inner join project p on
p.id = i.PROJECT
inner join issuetype ty on
ty.id = i.issuetype
where w.startdate >= '2019-01-01' and w.startdate <= '2019-02-01';

Use this prefix to extract the result in a CSV file

SELECT FROM WHERE INTO OUTFILE ‘file.csv’ FIELDS TERMINATED BY ‘,’

by MrAddon

Advertisements
Posted by:.

One thought on “Tempo Timesheets for Jira Software SQL worklogs extraction query

  1. Query updated:

    select distinct
    w.ID as "Worklog Id",
    w.issueid as "Issue Id",
    concat(p.pkey,"-",i.issuenum) as "Issue Key",
    i.summary as "Issue Summary",
    round(w.timeworked/3600,2) as "Hours",
    w.startdate as "Work Date",
    u.user_name as "User Name",
    u.display_name as "User Full Name",
    p.pkey as "Project Key",
    p.pname as "Project Name",
    ty.pname as "Issue Type Name",
    w.updated as "updated",
    w.updateauthor as "updated author",
    w.STARTDATE as "Start date"
    from worklog w
    inner join jiraissue i on
    i.id=w.issueid
    inner join cwd_user u on
    u.user_name=w.author 
    inner join project p on
    p.id = i.PROJECT
    inner join issuetype ty on
    ty.id = i.issuetype
    where w.startdate >= '2019-04-01' AND w.startdate <= '2019-04-30' AND u.directory_id = 10600 
    and w.AUTHOR = 'user.name'
    order by w.ID,u.display_name;
    
    

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s