How to retrieve all entries from worklog for given user and date?

I'm developing a JIRA 6 plugin which will count how much work the user has logged for current day. I don't need to know related issues, only total sum of logged hours matters.

When looking in the JIRA database, I see that worklog table has all the info I need - the author, the date and the time logged.

At first, I tried the following code to retrieve current user:

User user = 
                ComponentAccessor.getJiraAuthenticationContext()
                        .getUser().getDirectoryUser();

but I'm not sure if the author field of the worklog matches user.getName() or not?

Then I tried to get worklog entries by author:

List<Worklog> worklogs = ComponentAccessor.getWorklogManager(). ... // what do I do here?

There is no method getByAuthor or getByDate. What would be the simplest way to get all the Worklog entries for given user on given day?

3 answers

1 accepted

2 votes
Accepted answer

Finally I ended up with something like this. Maybe someone else will find it useful:

sqlProcessor = new SQLProcessor("defaultDS");

                sqlProcessor.prepareStatement("SELECT timeworked FROM worklog WHERE AUTHOR=? AND STARTDATE BETWEEN ? AND ?");
                
                sqlProcessor.setValue(userKey);
                
                // for some reason trying to pass Date directly throws 
                // java.sql.SQLException: Wrong data type: java.lang.IllegalArgumentException: 
                // Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
                sqlProcessor.setValue(sqlDateTime.format(startOfTodayCalendar.getTime()));
                sqlProcessor.setValue(sqlDateTime.format(endOfTodayCalendar.getTime()));   
                
                sqlProcessor.executeQuery();
                rs = sqlProcessor.getResultSet();
                
                while (rs.next()){

                    workSecondsSum += rs.getInt(1);
                    ...
                }

Thank you a lot, it will be very useful for me!

0 votes
Timothy Chin Community Champion Dec 01, 2013

Worklogs are tied to the issue. You might want to use a search to retrieve a selection of issues and loop through them using the WorklogManager.

Ok, so I should retrieve all the identifiers for issues which where updated on the given date and then retrieve all the worklogs for each issue and see if their AUTHOR field matches the given user. Seems a bit redundant data to read (I don't need issues, just their worklogs), considering this will be a scheduled task. But I guess I can redesign it with raw SQL for the worklog table, if I see some performance issues.

There are just some questions though:

- does logging work always update the UPDATED field of related issue, including also the cases when some old worklog item has been edited?

- what is stored in the AUTHOR field of worklog records? is it user name or user key? somehow I could find the answer in https://developer.atlassian.com/display/JIRADEV/Database+Schema

but I found a related bug:

https://jira.atlassian.com/browse/JRA-33074

From it I can assume that from JIRA v6 I should use the user key to identify authors of worklog items, right?

Also, as I'm a bit new to the SDK, I'm not sure what to use - SearchService or SearchProvider but I guess that's another question.

There is a problem with this approach in some exotic scenarios - issue update time might differ from the worklog startDate, thus I might miss some worklogs which were updated today but the time spent is for some other day, not today. I guess, I'll have to find a way to read it directly using SQL, and hoping that worklog table schema won't change in next JIRA update...

I have retrieved from 3 different tables from jira database.

 

Got the issue Key from Project and jiraissue table and mapped with worklog table

 

select p.pkey,j.issuenum,w.author,w.CREATED, (w.timeworked/60/60) as "Log work" 
from project p
inner join jiraissue j on p.id=j.project
inner join worklog w on w.issueid=j.id order by w.CREATED DESC;

 

Note: first column represents project key, second column issue number.

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted Thursday in Jira

Continuous visibility with the new Jira Software Cloud and Bitbucket Cloud integration

Hey Atlassian community, I am a PM on the Bitbucket Cloud team and I am excited to share that we just released an improvement to the Jira Software and Bitbucket Cloud integration. With the newly im...

76 views 0 3
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you