История состояний триггеров

[gospodin.horoshiy]

У вас активно используются функции которые Oracle only...
Вот как по аналогии в Mysql решил эту задачу, используя переменные:

select g.name,h.host,t.description,c.STARTTIME,c.ENDTIME, SEC_TO_TIME((UNIX_TIMESTAMP(c.ENDTIME)-UNIX_TIMESTAMP(c.STARTTIME))) DIFF
from (select objectid,value,

if(value=0 and @prevvalue=1 and @prevobject=objectid,FROM_UNIXTIME(@prevclock),NUL L) STARTTIME,

if(value=1 and @prevvalue=1,
FROM_UNIXTIME(clock+ least(0, @prevvalue:= value)+least(0, @prevobject:= objectid)),
FROM_UNIXTIME(clock+ least(0, @prevclock:= clock)+least(0, @prevvalue:= value)+least(0, @prevobject:= objectid))

) ENDTIME

from events,

(select (@prevclock := 0)) as bb,
(select (@prevvalue:=0)) as dd,
(select (@prevobject:=0)) as ee

order by objectid, clock) as c,

triggers t,
hosts h,
hosts_groups hg,
items i,
functions f,
groups g

where
c.value=0
and
c.STARTTIME IS NOT NULL
and
c.objectid=t.triggerid
and g.groupid=hg.groupid
and hg.hostid=h.hostid
and h.hostid=i.hostid
and i.itemid=f.itemid
and t.triggerid=f.triggerid
and UNIX_TIMESTAMP(c.`ENDTIME`)>=UNIX_TIMESTAMP(NOW())-86400

ORDER BY name,description, STARTTIME