maya.public

Parameters

Name	Type	Mode
serialnumber	character varying	IN
start_date	timestamp with time zone	IN
end_date	timestamp with time zone	IN
serialnumber	character varying	OUT
senddate	timestamp with time zone	OUT
creationdate	timestamp with time zone	OUT
etype	character varying	OUT
command_delay_secs	double precision	OUT
pos_delay_secs	double precision	OUT

Definition

with q1 as (select d.serialnumber,e.creationdate,e.senddate,lag(e.securitystatus,1) over w sw_prev,e.securitystatus sw_now from event e JOIN device d ON d.iddevice = e.iddevice WHERE d.serialnumber = $1 AND senddate BETWEEN $2 and $3  WINDOW w AS (partition by d.serialnumber order by e.senddate)), q2 as (SELECT serialnumber,senddate,creationdate,sw_prev,sw_now,'position' etype FROM q1 WHERE sw_prev <> sw_now  UNION SELECT d.serialnumber,senddate,null,null,null,'command' FROM commandexec ce JOIN device d ON d.iddevice = ce.iddevice WHERE d.serialnumber = $1 and senddate between $2 and $3 order by 1),q3 as (SELECT serialnumber,senddate,creationdate,etype,case when (etype = 'position' AND lag(etype,1) over w = 'command') OR (etype = 'command' AND lag(etype,1) over w = 'command') THEN extract('epoch' from (senddate - lag(senddate,1) over w)) END command_delay_secs FROM q2 WINDOW w as (PARTITION BY serialnumber ORDER BY senddate) ORDER BY 1,2) SELECT serialnumber,senddate,creationdate,etype,command_delay_secs,extract(epoch from (creationdate - senddate)) pos_delay_secs FROM q3 WHERE etype = 'command' OR command_delay_secs > 0 ;

ot_ro_command_delay(character varying, timestamp with time zone, timestamp with time zone)

Parameters

Definition