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

Posted by:.

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

  1. Query updated again:
    New definitive query with track of username name changes:

    (Atlassian official info: https://community.atlassian.com/t5/Jira-questions/What-about-the-data-in-app-user-table/qaq-p/787427 )

    New query (with join with table app_user):

    select
    w.ID as "Worklog Id",
    w.issueid as "Issue Id",
    concat(p.pkey,"-",i.issuenum) as "Issue Key",
    i.summary as "Issue Summary",
    format(w.timeworked/3600,2,'es_ES') as "Hours",
    date_format(w.startdate, '%d-%m-%Y') as "Work Date",
    date_format(w.startdate, '%M') as "Month",
    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",
    u.directory_id as "Directory",
    date_format(w.updated, '%d-%m-%Y') as "updated"
    from worklog w
    inner join jiraissue i on
    i.id=w.issueid
    inner join app_user app on
    app.user_key = w.author
    inner join cwd_user u on
    u.user_name= app.lower_user_name
    inner join project p on
    p.id = i.PROJECT
    inner join issuetype ty on
    ty.id = i.issuetype
    where w.startdate >= '2019-04-29' AND w.startdate <= '2019-09-30'
    and u.user_name = 'raul.pelaez'
    group by w.ID
    order by w.ID;
    
    

    Also we have added support to “GROUP BY 1” in order to do not duplicate the worklog line for the directory reason/problem. This sentence avoid/fixes the problem of the duplicate rows caused by directory_id username duplicated.

    Regards!

    Liked by 1 person

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