Trac query to show tickets that are past due date

Question

SELECT id AS ticket, status, priority, reporter, owner, 
       time AS created, summary
  FROM ticket
  WHERE status IN ('new', 'assigned', 'reopened') and reporter = $USER
  ORDER BY time, priority

I am trying to allow reporters to see their past due tickets that they have that are currently open but am new to trac. Any help using the current date is very much appreciated.

Answer 1

To accomplish the task you'll need to

• setup a custom ticket field due_date or similar (probably done already in your case)
• use a JOIN on Trac db table ticket_custom to get that fields values
• retrieve current date (time stamp)
• convert at least one of due_date and current date (probably both) for comparison

I'll skip 1 + 2 here, because there's existing documentation on StackOverflow (i.e. see How to show due date column in the trac reports?) and elsewhere.

Getting to NOW(): According to the authoritative wiki documentation on reports there is only one dynamically assigned variable for Trac reports: $USER. So you'll need to use an SQL function. I failed to find a portable solution, so these are suggestions depending on your db backend:

• SQLite (Trac default): current_date or datetime('now','localtime')
• PostgreSQL: current_timestamp or now()

On the last sub-task: Probably you're not (yet) using true custom time fields, as supported since Trac 1.1.1, because it is a development-snapshot release and still near bleeding-edge. I just point it out for completeness, because it'll save you the hassle of using string conversion workarounds as detailed below.

What you're likely using for now is a string, maybe with improved input method as provided by DateFieldPlugin. But a string value is initially useless for direct comparison inside the SQL statement. You'll have to convert all strings to date values or integer to get your report working. See How to create report with tickets closed at certain date in Trac as it is related to time stamp conversion. I'd recommend using the native Trac time column format (integer values), that is depending on your Trac version:

• Trac <= 0.11: POSIX seconds
• Trac >= 0.12: POSIX micro-seconds

But published examples advocate using a date function in the SQL statement (for SQLite). Depending on your date string format this will require string transformation into the appropriate format, i.e. using substr like shown in a sample report for DateFieldPlugin.

Putting the pieces together (example for SQLite, assuming MM/DD/YYYY as due_date string format):

SELECT id AS ticket, status, priority, reporter, owner, 
       time AS created, summary, tc.value as date
  FROM ticket t
LEFT JOIN ticket_custom tc on t.id = tc.ticket and tc.name= 'due_date'
  WHERE status IN ('new', 'assigned', 'reopened') and reporter = $USER
       and date(substr(tc.value,7,4)||"-"||substr(tc.value,1,2)||"-"||substr(tc.value,4,2)) < current_date
  ORDER BY time, priority