Get number of added hours in Trac's Estimation and Time Tracking plugins

Question

We are using Estimation and Time Tracking plugin for Trac.

I want to create the query that would show all additions of hours to tickets (As in Add Hours to Ticket).

Let's say we have developers: John, Fred and Peter and tickets Foo and Bar. I want to see something like this:

Ticket  Developer   At      HoursTotal
-------------------------------------
Bar     John        11:00   0.8 hours
Bar     Fred        10:13   2.0 hours
Foo     Peter       09:50   1.0 hours
Foo     John        09:00   0.5 hours

Answer 1

Silly me, something like that is included in the plugin. But this is what will create the table above:

 SELECT
   t.id AS ticket,
   t.summary AS summary,
   author,
   ticket_change.time AS datetime,
   newvalue AS HoursTotal
 FROM ticket_change
 JOIN ticket t ON t.id = ticket_change.ticket
 WHERE field = 'hours'