A quick look at the documentation shows that "changetime" is a field
on the ticket table which (you guessed it) stores the timestamp of the
last change to the ticket. (It's responsible for the "Last modified 5
days ago" line at the top of the ticket display) That means you don't
need to join any tables, which should make things easier. The tricky
thing about changetime is that it isn't a database-native timestamp
field, but an integer type field containing the timestamp in UNIX
epoch format.
The actual comparison in your WHERE clause is up to the database
engine you use. Here's what it would look like in Postgres:
to_timestamp(changetime) < now() - INTERVAL '7 days'
And so the whole query would be (using the default "Active Tickets"
report):
SELECT p.value AS __color__,
id AS ticket, summary, component, version, milestone, t.type AS
type,
(CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS
owner,
time AS created,
changetime AS _changetime, description AS _description,
reporter AS _reporter
FROM ticket t, enum p
WHERE status IN ('new', 'assigned', 'reopened')
AND p.name = t.priority AND p.type = 'priority'
AND to_timestamp(changetime) < now() - INTERVAL '7 days'
ORDER BY p.value, milestone, t.type, time
- Matthew Carlson
p.s. Before anyone jumps on me: I know there's probably a "standard
SQL" way to do this, I just don't know what it is.
On Mar 28, 1:03 pm, Steve <[EMAIL PROTECTED]> wrote:
> Can someone give me the query for this report?
>
> I just want to show all active tickets that no-one has updated/modifed
> in the past 7 days.
>
> thanks in advance!
>
> Steve
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
