Include date closed in Trac custom report

Question

I'm using the following to show tickets that have been closed, with newly closed tickets at the top:

SELECT 
   p.value AS __color__,
   id AS ticket, 
   summary, 
   component,
   version,
   milestone,
   t.type AS type, 
   owner, 
   status,
   time AS created,
   changetime AS _changetime,
   description AS _description,
   reporter AS _reporter
  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  WHERE status = 'closed'
  ORDER BY changetime DESC, time DESC, CAST(p.value AS integer), milestone, t.type, time

Here are the columns that currently show:

Ticket
Summary
Component
Version
Milestone
Type
Owner
Status
Created

I'd like to add the date closed to the report view (and perhaps a third column showing the date difference). How would I go about this?

Note though, that your concept has a flaw: Tickets are not r/o after closing, so last change may be for something else than status, think: further comments. So it depends entirely on organizational agreements to ensure, that status --> 'closed' is really the last change. IMHO not a reliable base for the report, but YMMV. — hasienda, Oct 27 '12 at 07:24
So there is not a way to accurately measure when tickets were actually closed? — a coder, Oct 27 '12 at 11:07
It is, in fact, this is a recurring question, that might be worth a cookbook example in Trac's wiki. I'll at a suggestion here too. — hasienda, Oct 29 '12 at 20:22

lc. · Accepted Answer · 2012-10-26T19:39:05.610

2

To show a field on the report view, as opposed to just the RSS feed, remove the leading underscore from the column name. changetime AS _changetime is the offending line; try changing it to changetime AS closed. Note: you may have to fix it up like datetime(changetime/1000000, 'unixepoch') AS closed.

To get the date difference, try subtracting the two columns, each wrapped in a call to the SQLite julianday function, like julianday('now') - julianday(changetime/1000000, 'unixepoch') AS closedago.

edited Oct 26 '12 at 19:39

answered Oct 26 '12 at 19:33

lc.

113,939
20
158
187

Ok this kind of works -- closed appears but has (unixtime?) as the date: 1351278660897600. If I change it to changetime as date, a formatted date appears, but the column heading just says "Date". – a coder Oct 26 '12 at 19:36
No luck with changetime as closed - still getting the timestamp. 'date' will work ok. – a coder Oct 26 '12 at 19:39
I'm getting this with the julianday function: | 10/11/11 created | 10/17/12 closed | -1.35049337095e+15 | – a coder Oct 26 '12 at 19:41
Oh shoot I think you have to divide by 1000000... they're microsecond unixepoch timestamps. – lc. Oct 26 '12 at 19:41
Still a bit off with this: ( (julianday('now') - julianday(changetime, 'unixepoch'))/1000000 ) as closedago --- Just getting the closed date is helpful. Thanks. – a coder Oct 26 '12 at 19:46
1

@acoder Check my edit. It's not the day you have to divide, it's the value in `changetime`. – lc. Oct 26 '12 at 19:47
Just got your edit -- getting much warmer. Just need to round the digit now. (off to goog) – a coder Oct 26 '12 at 19:48

score 1 · Answer 2 · answered Oct 29 '12 at 20:37

1

Adding the closed date is not as straight-forward as one me think. The following example will tell you the trick:

SELECT p.value AS __color__,
       ticket, summary, component, version, milestone, t.type, owner,
       t.time AS created,
       MAX(tc.time) as date
  FROM ticket_change tc
  LEFT JOIN ticket t ON tc.ticket=id
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
 WHERE field='status'
   AND newvalue='closed'
 GROUP by ticket
 ORDER BY tc.time DESC, t.time DESC, CAST(p.value AS integer), milestone, t.type

You need the JOIN on the 'ticket_change' db table and last-first sorting to reliably get the last close date.

answered Oct 29 '12 at 20:37

hasienda

2,390
1
13
16

Report execution failed: ProgrammingError: column "p.value" must appear in the GROUP BY clause or be used in an aggregate function LINE 2: SELECT p.value AS __color__, ^ – emecas Mar 06 '14 at 08:41
What Trac version has this issue? I've been testing with Trac 1.1.1. – hasienda Mar 09 '14 at 18:45
I guess, this is the reason, I'm working with 1.0, Once I upgrade it I will try again, thanks! – emecas Apr 19 '14 at 11:26

Include date closed in Trac custom report

2 Answers2