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

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);
                    ...
                }