Das deutsche Python-Forum

Code: Alles auswählen

SELECT
   t.id AS ticket,
   t.summary,
   t.status,
   t.type,
   t.time,
   to_timestamp(t.time/1000000) AS createtime,
   DATE(to_timestamp(t.time/1000000)) AS __group__,
   to_timestamp(so.time/1000000) AS oldvaluetime
FROM ticket t

LEFT JOIN 
(
SELECT
   tc.ticket,
   MIN(tc.time) AS time
FROM ticket_change tc
WHERE tc.field='status'
AND tc.time/1000000 <= EXTRACT(EPOCH FROM DATE($START) + INTERVAL '1 day')
AND tc.time/1000000 >= EXTRACT(EPOCH FROM DATE($START) - INTERVAL '1 week')
AND tc.oldvalue IN ('new','assigned','accepted') 
AND tc.newvalue IN ('closed')
GROUP BY tc.ticket
) so ON so.ticket=t.id

ORDER BY t.time DESC