Tempo Timesheets for Jira Software SQL worklogs extraction query

We have talked about the Servlet extraction of worklogs:

• How to extract an Excel report from Tempo Timesheets using an URL

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

• How to list your Jira Tempo Teams and members in a Confluence page
• Jira Groovy example script to get Tempo Teams Worklogs using REST API
• How to get the required team hours from JIRA Tempo plugin with groovy
• Jira Groovy Postfunction example to set Tempo Team and Tempo Account fields.
• Groovy REST WS call example to TEMPO JIRA plugin

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