CREATE OR REPLACE FUNCTION icinga_availability (id BIGINT, start_date TIMESTAMP, end_date TIMESTAMP)
  RETURNS DECIMAL(7, 4) 
STABLE
AS
$Q$
	with
	params as
	(
		select
			$1::bigint as id,
			$2::timestamp as start_date,
			$3::timestamp as end_date
	),
	uptimeEvents as (
		-- Start of downtimes
		select	object_id,
			actual_start_time as event_time,
			0 as up,
			1 as fallback_up
		from icinga_downtimehistory

		union all
		
		-- End of downtimes
		select	object_id,
			actual_end_time as event_time,
			1 as up,
			0 as fallback_up
		from icinga_downtimehistory

		union all

		-- Uptime events between downtimes
		select	object_id,
			state_time as event_time,
			case when (objecttype_id  = 1 and state = 0) then 1
			     when (objecttype_id <> 1 and state IN (0,1)) then 1
			     else 0 end as up,
			case when (objecttype_id  = 1 and last_state = 0) then 1
			     when (objecttype_id <> 1 and last_state IN (0,1)) then 1
			     else 0 end as fallback_up
		from icinga_statehistory natural join (select object_id, objecttype_id from icinga_objects) objecttype
		where state_type =  1 /* hard */
		  and not exists (
			select 1 from icinga_downtimehistory
			where icinga_downtimehistory.object_id = icinga_statehistory.object_id
			  and state_time between actual_start_time and actual_end_time)
	),
	uptimePeriods as
	(
		-- All periods ending with an uptime event
		select	object_id,
			lag(event_time) over (partition by object_id order by event_time asc) as start_time,
			event_time as end_time,
			coalesce(
				lag(up) over (partition by object_id order by event_time asc),
				fallback_up) as up
		from uptimeEvents

		union all

		-- Add a period starting at the last uptime event
		select distinct -- pg 8.4 doesn't support orderd aggregates
			object_id,
			last_value(event_time) over (
				partition by object_id 
				order by event_time asc
				ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as start_time,
			null::timestamp as end_time,
			last_value(up) over (
				partition by object_id 
				order by event_time asc 
				ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as up
		from uptimeEvents	
	),
	uptimePeriodsInDateRange as
	(
		select	object_id,
			greatest(start_time, start_date) as start_time,
			least(end_time, end_date) as end_time,
			up
		from uptimePeriods ,params
		where object_id = id
		  and (end_time is null or end_time > start_date)
		  and (start_time is null or start_time < end_date)
	),
	availability as
	(
		select	object_id,
			sum(unix_timestamp(end_time) - unix_timestamp(start_time)) 
			/ (select unix_timestamp(end_date) - unix_timestamp(start_date) from params) * 100  as availability
		from uptimePeriodsInDateRange
		where up = 1
		group by object_id
	)
	select availability from availability;
$Q$ LANGUAGE sql;

select icinga_availability(5, '2013-01-01', '2013-07-01')