Difference between revisions of "Public.getoutagetimeinwindow (function)"

From dbscript Online Help
Jump to: navigation, search
 
(One intermediate revision by the same user not shown)
Line 2: Line 2:
  
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
|-
+
|- valign="top"
 
| '''Function
 
| '''Function
 
| public.getoutagetimeinwindow
 
| public.getoutagetimeinwindow
|-
 
 
|}
 
|}
 
  
 
=== Source ===
 
=== Source ===
Line 93: Line 91:
 
$function$
 
$function$
 
</pre>
 
</pre>
 
=== References ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
| '''Dependency Type
 
| '''Object Type
 
| '''Referenced Object
 
 
|-
 
| Select
 
| Table
 
| [[public.outages_(table)|public.outages]]
 
 
|}
 
 
=== Dependencies ===
 
{| border="1" cellpadding="5" cellspacing="0" style="border-collapse:collapse"
 
|- style="background:silver"
 
| '''Reference Type
 
| '''Object Type
 
| '''Referencing Object
 
 
|-
 
| Execute
 
| Function
 
| [[public.getmanagedoutageforintfinwindow_(function)|public.getmanagedoutageforintfinwindow]]
 
 
|-
 
| Execute
 
| Function
 
| [[public.getmanagedoutagefornodeinwindow_(function)|public.getmanagedoutagefornodeinwindow]]
 
 
|-
 
| Execute
 
| Function
 
| [[public.getpercentavailabilityinwindow_(function)|public.getpercentavailabilityinwindow]]
 
 
|}
 

Latest revision as of 10:19, 27 August 2011

wikibot[edit]

Function public.getoutagetimeinwindow

Source[edit]

CREATE OR REPLACE FUNCTION public.getoutagetimeinwindow(integer, character varying, integer, timestamp without time zone, timestamp without time zone)
 RETURNS double precision
 LANGUAGE plpgsql
AS $function$
   DECLARE
	nid ALIAS FOR $1;
	ipid ALIAS FOR $2;
	sid ALIAS FOR $3;
	xtime ALIAS FOR $4;
	ytime ALIAS FOR $5;
	orec RECORD;
	lostTime timestamp without time zone;
	gainTime timestamp without time zone;
	downtime float8;
	zero CONSTANT float8 := 0.0;
	epochTime CONSTANT timestamp without time zone := to_timestamp('01 Jan 1970 00:00:00', 'DD Mon YYYY HH24:MI:SS');
   BEGIN
   	downtime = zero;
   	FOR orec IN SELECT ifLostService,ifRegainedService
		FROM outages WHERE (nodeID = nid AND ipAddr = ipid AND serviceID = sid AND
		                   ((ifRegainedService IS NULL AND ifLostService <= xtime)  OR
				    (ifRegainedService > ytime)))
	LOOP
	 BEGIN
		gainTime := epochTime;
		lostTime := orec.ifLostService;
		IF orec.ifRegainedService IS NOT NULL THEN
			gainTime := orec.ifRegainedService;
		END IF;
		--
		-- Find the appropriate records
		--
		IF xtime > lostTime THEN
		 --
		 -- for any outage to be in window of 
		 -- opportunity the lost time must ALWAYS be
		 -- less that the x time.
		 --
		 IF gainTime = epochTime THEN
		  --
		  -- if the gain time is epochTime then the outage
		  -- does not have an uptime.
		  --
		   IF ytime > lostTime THEN
		    downtime := downtime + EXTRACT(EPOCH FROM (xtime - ytime));
		   ELSE
		    downtime := downtime + EXTRACT(EPOCH FROM (xtime - lostTime));
		   END IF;
		 ELSE
		  IF xtime > gainTime AND gainTime > ytime THEN
		   --
		   -- regain time between x & y
		   --
		    IF ytime > lostTime THEN
		     downtime := downtime + EXTRACT (EPOCH FROM (gainTime - ytime));
		    ELSE
		     downtime := downtime + EXTRACT (EPOCH FROM (gainTime - lostTime));
		    END IF; 
		  ELSE
		   IF gainTime > xtime THEN
		   --
		   -- regain time greater than x, lost less that x
		   --
		    IF ytime > lostTime THEN
		     downtime := downtime + EXTRACT (EPOCH FROM (xtime - ytime));
		    ELSE
		     downtime := downtime + EXTRACT (EPOCH FROM (xtime - lostTime));
		    END IF;
		   -- end gainTime > xtime
		   END IF;
		  -- end xtime > gainTime AND gainTime > ytime
		  END IF;
		 -- end gaintime == epochTime
		 END IF;
		-- end xtime > lostTime
		END IF;
	 END;
	END LOOP;
	RETURN downtime*1000.0;
   END;
$function$