*bump*
I've reworked my custom query to be as follows:
SELECT p.value AS __color__,
id AS ticket, summary, t.type AS type,
(CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS owner,
t.time AS created, tc.time AS updated
FROM ticket t
LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
LEFT JOIN (select * from ticket_change where field='comment' group by
ticket) tc ON tc.ticket = t.id
WHERE status IN ('new', 'assigned', 'reopened')
ORDER BY p.value, t.type, t.time
I have tried casting the tc.time to datetime to no avail also.
However when I display the query I get the following output:
Ticket Summary Type Owner Created Updated
#54 <some summary> Enhancement owner1 * 02/28/07
1173314590
#43 <some summary> Enhancement owner2 * 02/27/07
1172800087
#104 <some summary> Enhancement owner2 * 03/08/07
1173465720
#112 <some summary> Enhancement owner2 * 03/09/07
1173492583
#24 <some summary> Problem owner2 02/23/07 None
#26 <some summary> Problem owner2 02/23/07 None
#27 <some summary> Problem owner2 * 02/23/07 1172531477
I need the Updated field to show up as a Date, not an integer
representation.
Any ideas??
-Andrew
Andrew Nelson wrote:
> I've modified the standard report 1 to also show when comments were made
> to a ticket. The following is the SQL statement I'm using:
>
> 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,
> t.time AS created, tc.time AS updated
> FROM ticket t
> LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
> LEFT JOIN ticket_change tc ON tc.field = 'comment' and tc.ticket = t.id
> WHERE status IN ('new', 'assigned', 'reopened')
> ORDER BY p.value, milestone, t.type, t.time
>
>
> The "updated" column does not show as a date as I would like it to, it
> only shows up as an integer. How do I get Trac to do this? We are
> running 10.3.
>
> Thanks
>
> -Andrew
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
