CookBook/Trac

Trac Tickets Created or Closed by Month

The ticket or ticket_change table can be used to detect resolved tickets, though each has slightly different implications.

The changetime field in the ticket table can refer to any change, so if there were further changes to the ticket after it was marked resolved, the date will reflect those changes instead.

The ticket_change table records all changes to tickets by field, but will result in a slower report. If a ticket was closed multiple times (i.e. it was reopened then closed again), it will also be reported as separate entries.

SELECT
strftime('%Y-%m', time, 'unixepoch') || " Created" as __group__,
id AS ticket, 
status,
owner,
time AS created,
changetime as modified,
summary
FROM
ticket

UNION

SELECT
strftime('%Y-%m', changetime, 'unixepoch') || " Closed" as __group__,
id AS ticket, 
status,
owner,
time AS created,
changetime as modified,
summary
FROM
ticket
WHERE
status = 'closed'
SELECT
strftime('%Y-%m', time, 'unixepoch') || " Created" as __group__,
id AS ticket, 
status,
owner,
time AS created,
changetime as modified,
summary
FROM
ticket

UNION

SELECT
strftime('%Y-%m', ticket_change.time, 'unixepoch') || " Closed" as __group__,
id AS ticket,
status,
owner,
ticket.time AS created,
ticket_change.time as modified,
summary
FROM
ticket, ticket_change
ON
ticket.id = ticket_change.ticket
AND
ticket_change.field = 'status'
AND
ticket_change.newvalue = 'closed'
WHERE
status = 'closed'