-- ## 54: All tickets I participated in (by time) ## --

-- Here are all the tickets you participated in (including closed tickets), by last modified time:

SELECT p.value AS __color__,
   id AS ticket, 
   (CASE WHEN EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) - changetime/1000000 > 2 * 86400
         THEN round(CAST((EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) - changetime/1000000) / 86400 AS numeric), 0) || ' days'
         WHEN EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) - changetime/1000000 > 3600
         THEN round(CAST((EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) - changetime/1000000) / 3600 AS numeric), 0) || ' hours'
         WHEN EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) - changetime/1000000 > 60
         THEN round(CAST((EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) - changetime/1000000) / 60 AS numeric), 0) || ' mins'
         ELSE round(CAST(EXTRACT(EPOCH FROM CURRENT_TIMESTAMP) - changetime/1000000 AS numeric), 0) || ' secs'
   END) AS Lastmod,
   milestone,
   summary, status, 
   t.type AS type, priority,
   changetime AS _changetime, description AS _description
  FROM ticket t, enum p
  WHERE (t.id in (SELECT ticket from ticket_change where author='$USER') OR t.reporter = '$USER')
AND p.name = t.priority AND p.type = 'priority'
  ORDER BY changetime desc