Trac Report SQL Query and Last Modified Date

[zdgman]

Hello All,

I am trying to get the lastmodified column in this query on the Trac
Dev Site

http://trac.edgewall.org/report/20

I know how to create lastmodified as a date but can't seem to get it
listed out as a day's since last updated. Would it be possible for me
to see the SQL query from the edgewall site?

Thanks.

[Erik Bray]

If you want the number of days since the ticket was last modified,
that's just simple math: Take the current date/time as a unix
timestamp, subtract the modified time, and divide by (3600 * 24).

[zdgman]

I have something like this going right now. Decided I was going to try
and do it in hours and then work it to days

strftime('%s','now') - strftime('%s','changetime') / 3600

That doesn't seem to work at present although I am pretty sure that it
should.

[zdgman]

Here is the actual query (I did not write this) that works for me,
just in case anyone else needs:

SELECT p.value AS __color__,
id AS ticket, summary, component, version, milestone, t.severity AS
severity,
(CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS
owner,
(CASE WHEN strftime('%s','now') - changetime > 86400
THEN round((strftime('%s','now') - changetime)/86400,0) || '
days'
WHEN strftime('%s','now') - changetime > 3600
THEN round((strftime('%s','now') - changetime)/3600,0) || '
hours'
ELSE (strftime('%s','now') - changetime)/60 || ' mins'
END) AS Modified,
author as Modified_By,
description AS _description,
reporter AS _reporter
FROM ticket t, enum p,
(SELECT ticket, author, MAX(time) FROM ticket_change WHERE field
= 'comment' GROUP BY ticket) tc
WHERE status IN ('new', 'assigned', 'reopened')
AND p.name = t.priority AND p.type = 'priority'
AND t.id = tc.ticket
ORDER BY changetime desc, p.value, milestone, t.severity, time