[Trac-tickets] Re: [The Trac Project] #2288: Date/time based ticket queries

[The Trac Project]

#2288: Date/time based ticket queries
--------------------------------------+-------------------------------------
 Reporter:  trac at szynaka.com          |        Owner:  jonas
     Type:  enhancement               |       Status:  new  
 Priority:  normal                    |    Milestone:  1.0  
Component:  ticket system             |      Version:  0.9  
 Severity:  normal                    |   Resolution:       
 Keywords:  ticket custom query date  |  
--------------------------------------+-------------------------------------
Comment (by garyo):

 We are doing this now with 0.9b2, but it is a little painful.  We use the
 sqlite date/time functions to process the times into dates.  Here's our
 ''Tickets closed recently'' report:

 {{{
 #!sql
 select id, summary, component, milestone, owner,
   (select max(tc.time) from ticket_change tc
      where t.id = tc.ticket and field = 'status' and newvalue = 'closed')
    as closetime,
   datetime((select max(tc.time) from ticket_change tc
      where t.id = tc.ticket and field = 'status' and newvalue = 'closed'),
             'unixepoch', 'localtime') as closedate
   from ticket t
 where status = 'closed'  and closetime > strftime('%s',datetime("now","-2
 days"))+0
  order by closetime;
 }}}

 Perhaps that will help someone...

-- 
Ticket URL: <http://projects.edgewall.com/trac/ticket/2288>
The Trac Project <http://trac.edgewall.com/>