Trac Report sqlite 0.12.5

[anonymous]

I'm working on modifying the active tickets by milestone report and I'm having some strange results

My Query:

SELECT p.value AS __color__,
   t.milestone AS __group__,
   (CASE status 
      WHEN 'closed' THEN 'color: #777; background: #ddd; border-color: #ccc;'
      ELSE 
        (CASE owner WHEN $USER THEN 'font-weight: bold' END)
    END) AS __style__,
   id AS ticket, summary, component, status, 
   resolution,version,
   t.type AS type, 
   priority, 
   owner,
   changetime AS modified,
   time AS created,
   reporter AS _reporter,
   case when status = 'closed' then changetime end AS ClosedDate,
   case when status = 'closed' then (julianday(changetime)-julianday(time)) Else
   (julianday('now')-julianday(time))  end AS DaysOpen

  FROM ticket t
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  ORDER BY (milestone IS NULL), milestone DESC, (status = 'closed'), 
        (CASE status WHEN 'closed' THEN changetime ELSE (-1) * CAST(p.value AS integer) END) DESC

07/14/16 07/07/16 1468520856162011 670206529570.0

07/13/16 05/26/16 1468421289113665 4.14567222519e+12

I don't understand why changetime shows up as date formatted in the modified column but shows up as unixepoch time in the closeddate column.

Further some of the results in the Days Open field are accurate and some are not.

When I try to do formatting with date() or datetime() I get really inaccurate results. I'm kind of at a loss.

[Jun Omae]

InstallationIssue. Support and installation questions should be asked on the mailing list or IRC channel.

Also, date, created and modified fields in SQL are formatted as date. See tags//trac-0.12.5/trac/ticket/templates/report_view.html#L162​.