CODEWITHSUNDEEP
reporttrac
Hpatoio
Hpatoio

Reputation: 1815

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

Upvotes: 1

Views: 544

Answers (2)

hasienda
hasienda

Reputation: 2390

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

Upvotes: 0

RjOllos
RjOllos

Reputation: 2934

The time at which the ticket was closed can be obtained in the ticket_change table by searching for entries with field=resolution and newvalue=closed. There could be multiple such entries for a single ticket if it was reopened and then closed again. The tables are documented here. See also #8728.

Upvotes: 1

Related Questions