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?

Answer 1

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.