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

[Maris Seimanovs]

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?

[Maris Seimanovs · Accepted solution]

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!

[suresh chitturi]

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.

[Timothy]

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.

[Maris Seimanovs]

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.

[Maris Seimanovs]

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...