如何在Trac中创建在特定日期关闭票据的报告

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';

如果您还知道年份,那么最好使用vartec_s建议的表达式,而不是strftime:

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')

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 */

如果您需要特定月份:

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') 

在哪里? date('2009-01-01','start of month') 是由日期给定的一个月的第一天,并且 date('2009-01-01','start of month','+1 month','-1 day') 是一个月的最后一天。

另外,关于表结构,这里是:

CREATE TABLE ticket_change ( 
    ticket   INTEGER,
    time     INTEGER,
    author   TEXT,
    field    TEXT,
    oldvalue TEXT,
    newvalue TEXT,
    UNIQUE ( ticket, time, field ) 
);