Search code examples
reporttrac

Trac report closed ticket by date

I need a Trac report that show me all closed ticket grouped by closing date. Is this possible ?

I've tried to use this statement but with no luck

SELECT component, summary, priority, time AS created, strftime("%yyyy-%m-%d",time) AS __GROUP__, t.description AS _description, reporter AS reporter FROM ticket t LEFT JOIN milestone m ON m.name = t.milestone WHERE t.status = 'closed' ORDER BY m.due DESC

Solution

  • Here is a query that works (tested in Trac 1.1.1):

    SELECT date(MAX(tc.time/1000000), 'unixepoch') as __group__,
       ticket, summary
  FROM ticket_change tc
  LEFT JOIN ticket ON tc.ticket=id
 WHERE field='status'
   AND newvalue='closed'
 GROUP by ticket
 ORDER by tc.time

    Critical as well as tricky bits are

    • finding the LAST closed date
    • properly formatting dates by converting time stamps to string without interfering with 'automagical' time-stamp-to-date-string conversion for reports