If you use sqlite, this can be helpful:
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
Cheers / Erik
On Nov 29, 2007 11:50 AM, the_J0ker <[EMAIL PROTECTED]> wrote:
>
> This would give you all closed tickets in the last 1 month:
> ____________________________________________
> SELECT DISTINCT
>
> id AS ticket, time, owner, version, milestone, resolution, status
>
> FROM ticket t
> WHERE status IN ('closed')
> AND time >= (select strftime ("%s", "now", "-1 month"))
> ORDER BY time
> ____________________________________________
>
> or if you wanted all in the current calendar month you could change
> the select
> line to:
> AND time >= (select strftime ("%s", "now", "start of month"))
>
>
> Another report I have is for selecting defects and enhancement tickets
> which
> were closed between two dates:
> ___________________________________________
> select id, type, summary, component, milestone, owner,
>
> datetime((select max(tc.time) from ticket_change tc
> where t.id = tc.ticket and field = 'status' and newvalue =
> 'closed'),
> 'unixepoch', 'localtime') as closedate,
> (select max(tc.time) from ticket_change tc
> where t.id = tc.ticket and field = 'status' and newvalue =
> 'closed')
> as closetime
> from ticket t
> where status = 'closed'
> and closetime > strftime('%s',datetime('2006-03-24 16:38:45')) +0
> and closetime < strftime('%s',datetime('2006-04-06 15:25:34')) +0
> AND type IN ('defect','enhancement')
> order by -closetime;
> ___________________________________________
>
>
> On Nov 28, 5:30 pm, [EMAIL PROTECTED] wrote:
> > Folks,
> >
> > I'm a bit new to the TRACS reporting and my SQL skills are non-
> > existent. However I need to generate reports from tickets we put into
> > TRACS.
> >
> > I want to create a report that will only give me closed tickets for a
> > specified date range (typically, 1 month like you can do in the
> > timeline view). It would also be nice (maybe as a seperate report) to
> > collate closed tickets by component and a specified date range.
> >
> > Is this possible? Does anyone know how to do it?
> >
> > thanks
> >
> > Pat
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "Trac
Users" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/trac-users?hl=en
-~----------~----~----~----~------~----~------~--~---
