Bug creating new report with custom query

[msbrogli@…]

I got an internal error when I create a new report using the following tag:

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,
   datetime(time, 'unixepoch') 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 IN ('new', 'assigned', 'reopened') 
  ORDER BY p.value, milestone, t.type, time

the problem is with datetime command.. it's accepted by sqlite (i tried in sqlite console and worked fine).

[osimons]

Problem is that Trac does not understand the custom formatted dates it receives from the SELECT. That is also why you get an error messsage (that you could have included) that (at least for my 0.11b1) says something like:

ValueError: invalid literal for int(): 2007-11-29 23:23:35

For 0.10 it stores and uses unix timestamps, and for 0.11 it still stores timestamps but convert it to Python datetime objects on the fly for all internal use. The formatting is controlled by your Python / Server setttings and can only be changed there - not on input from the database.

For your report, you need to change time back to plain; time AS created.