How would you modify this trac query to also include due date?

We have SQL query for repor开发者_运维问答ts:

SELECT p.value AS __color__,owner AS __group__,id AS ticket, severity,
    priority, status, summary, component, milestone, t.type AS type, 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 = 'assigned' OR status = 'new'
ORDER BY owner, p.value, t.type, time

We need to add one more column to this : duedate in mm/dd/yy format

duedate is a field coming from Custom Field plugin

How would you modify the query to support this?

First, to retrieve your duedate values you'll need another JOIN, but this time to ticket_custom Trac db table, where all custom ticket field values reside. But this has been already explained in detail in another question here.

Second, custom field values can't be re-formatted on-the-fly, what is especially true for date/time fields. Again more details are available in the SO question linked above. You'll have to live with your input, adopt code form the Custom Time Field proposal or hack on your own (not recommended at all).