Report Query with dates

[szybalski@…]

Hello, Based on the reports wiki page, trac wants to disable the reports in future version? For now the only way to save a query out of the box is via report. Below I will copy paste some reports that would be nice to include in the reports wiki as a custom advenced query examples. It took me over a week to get to this point. If what I supply here you can start doing queries in few minutes.

Some ideas taken from ticket #2288

Most of the problems come with knowing the fields which these examples show, and manipulating the date and date functions for particular database api. Below you will find examples on sqlite and mysql date functions and how to convert from timestamp.

sqlite:

recently closed

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","-30 days"))+0
 order by closetime

Mysql: Open ticket count by milestone. This is somewhat management report to see how many tickets are coming in daily.

SELECT version as __group__,DATE(FROM_UNIXTIME(time)) as Created, milestone, count(*) AS 'Count of Tickets'
  FROM ticket
  WHERE time > UNIX_TIMESTAMP(NOW() - INTERVAL 7 DAY)
GROUP BY version,DATE(FROM_UNIXTIME(time)),(milestone IS NULL), milestone
ORDER BY version,DATE(FROM_UNIXTIME(time)) DESC ,(milestone IS NULL), milestone

sqlite version

SELECT version __group__ ,date(time,'unixepoch', 'localtime') as Created,milestone, count(*) AS 'Count of Tickets'
  FROM ticket
  WHERE time > strftime('%s',datetime("now","-7 days"))+0
GROUP BY version,date(time,'unixepoch', 'localtime'),(milestone IS NULL), milestone
ORDER BY version,date(time,'unixepoch', 'localtime') DESC ,(milestone IS NULL), milestone

I'll try to attach the remaining reports that I still need to do. IF you have any popular reports please paste them in here.

What I need:

1. Closed tickets count by day and milestone,
2. Open tickets count by milestone
3. Active ticket count by user
4. Closed ticket count by user who closed it.

If you have any of these done, or something similar please past it in.

Thanks, Lucas

[szybalski@…]

Recently Closed in mysql version (for manager)(count would be better):

select id, summary, component, milestone, owner ,
(select tc.author from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue = 'closed' order by tc.time DESC limit 1) as ModifiedBy, 
  (TIME(FROM_UNIXTIME((select MAX(tc.time) from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue = 'closed')))) as closetime,
  (DATE(FROM_UNIXTIME((select MAX(tc.time) from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue = 'closed')))) as closedate
  from ticket t
where t.status = 'closed' and (select MAX(tc.time) from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue = 'closed')  > UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY) 
  order by closedate DESC,closetime DESC

My recently Closed. Working but might need more optimizing.

select id, summary, component, milestone, owner ,
(select tc.author from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue = 'closed' order by tc.time DESC limit 1) as ModifiedBy, 
  (TIME(FROM_UNIXTIME((select MAX(tc.time) from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue = 'closed')))) as closetime,
  (DATE(FROM_UNIXTIME((select MAX(tc.time) from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue = 'closed')))) as closedate
  from ticket t
where t.status = 'closed' and (select MAX(tc.time) from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue = 'closed')  > UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY) 
  order by closedate DESC,closetime DESC

accepted tickets by user count , mysql version (needed to have a count of peoples workload)

SELECT owner AS __group__,
   milestone,component,DATE(FROM_UNIXTIME(time)) as Created,
   changetime AS _changetime, 
   reporter AS _reporter, count(*) AS 'Count of Tickets'
  FROM ticket t
  WHERE status = 'accepted' OR status = 'assigned'
GROUP BY owner, DATE(FROM_UNIXTIME(time)),version,(milestone IS NULL), milestone
ORDER BY owner, DATE(FROM_UNIXTIME(time)) DESC ,version,(milestone IS NULL), milestone

Entered tickets by milestone, mysql (Good for daily count of how much work came in.)

SELECT DATE(FROM_UNIXTIME(time)) as __group__,DATE(FROM_UNIXTIME(time)) as Created,version, milestone, count(*) AS 'Count of Tickets'
  FROM ticket
  WHERE time > UNIX_TIMESTAMP(NOW() - INTERVAL 7 DAY)
GROUP BY DATE(FROM_UNIXTIME(time)),version,(milestone IS NULL), milestone
ORDER BY DATE(FROM_UNIXTIME(time)) DESC ,version,(milestone IS NULL), milestone

[szybalski@…]

​http://groups.google.com/group/trac-users/browse_thread/thread/c3452e8656af5988/62c9ebdee6ec91ea?hl=en&lnk=gst&q=datetime#62c9ebdee6ec91ea

Samples from mailing list. Verified by user on the mailing list to work.

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; 

[Christian Boos]

CookBook/Configuration/Reports?

[anonymous]

Closed tickets by user (slow) This reports tells you how many tickets were closed by what user. At hight speed task tracking this is very useful measurement of performance.

select milestone, 
(select tc.author from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue = 'closed' order by tc.time DESC limit 1) as ModifiedBy, 
   (DATE(FROM_UNIXTIME((select MAX(tc.time) from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue = 'closed')))) as closedate, count(*) AS 'Count of Tickets'
  from ticket t
where t.status = 'closed' and (select MAX(tc.time) from ticket_change tc
     where t.id = tc.ticket and field = 'status' and newvalue = 'closed')  > UNIX_TIMESTAMP(NOW() - INTERVAL 7 DAY) 
  group by ModifiedBy,milestone,closedate
  order by ModifiedBy,milestone,closedate DESC

[szybalski@…]

One more… Tickets Over 15 Days.

This report tells you what tickets are outstanding. You can pick a time frame based on you requirements. Any tickets over 15 days can be seen and user can make them critical if he believes its taking too long to fix them.

SELECT DATE(FROM_UNIXTIME(time)) as __group__,DATE(FROM_UNIXTIME(time)) as Created,version, milestone, count(*) AS 'Count of Tickets'
  FROM ticket
  WHERE time < UNIX_TIMESTAMP(NOW() - INTERVAL 15 DAY) and status <> 'closed'
GROUP BY DATE(FROM_UNIXTIME(time)),(milestone IS NULL), milestone,version
ORDER BY DATE(FROM_UNIXTIME(time)) DESC ,(milestone IS NULL), milestone,version

[Christian Boos]

We should really provide some ways to abstract working with dates…

As for the list of useful examples, please really do that in a CookBook page, like I advised in comment:3.

[Christian Boos]

Milestone 2.0 deleted