views:

726

answers:

2

I wish to create a report that would list all the tickets that were closed in a certain period of time.

The pseudo-code would be like

SELECT * FROM tickets
WHERE closed AND date_closed = 'january 2009'

The part I am unable to resolve is date_closed = 'january 2009'.

Is there a way to do it in Trac?

I am not interested in particular SQL syntax, I can write the time constrictions myself. What I am not certain about is Trac's db structure.

+2  A: 
SELECT * FROM ticket
WHERE status='closed' 
  AND date(changetime,'unixepoch') 
      BETWEEN date('YYYY-MM-DD') /* <- here goes your start date */
          AND date('YYYY-MM-DD') /* <- here goes your end date */

If you want a specific month:

SELECT * FROM ticket
WHERE status='closed' 
  AND date(changetime,'unixepoch') 
      BETWEEN date('2009-01-01','start of month') 
          AND date('2009-01-01','start of month','+1 month','-1 day')

Where date('2009-01-01','start of month') is the first day of the month given by date, and date('2009-01-01','start of month','+1 month','-1 day') is the last day of the month.

vartec
Last change may be for something else than status.
kmkaplan
Good point kmkaplan. However in my case, I am interested in tickets that are closed at this moment, so "closed" is always the last change. Anyway. Both of yours helped me much. Thanks a lot.
Josef Sábl
@JS: even in that case you can change the ticket, thus its changetime, when it is closed.
kmkaplan
+1  A: 
SELECT DISTINCT ticket.* FROM ticket, ticket_change
 WHERE ticket.id = ticket_change.ticket
   AND ticket_change.field = 'status'
   AND ticket_change.newvalue = 'closed'
   AND strftime('%m', ticket_change.time, 'unixepoch') = '01';

If you also know the year, instead of strftime you’d better use an expression like vartec’s suggested:

SELECT DISTINCT ticket.* FROM ticket, ticket_change
 WHERE ticket.id = ticket_change.ticket
   AND ticket_change.field = 'status'
   AND ticket_change.newvalue = 'closed'
   AND date(ticket_change.time,'unixepoch') 
       BETWEEN date('2009-01-01','start of month') 
           AND date('2009-01-01','start of month','+1 month','-1 day')
kmkaplan