I have postgresql 9.4 and wrote a function get_ignition_time() to get the first time when a car was ignition on and the last time when ignition is off, those
time stamps are used in another function get_fuel_consumption() to get the fuel consumption.
The issue is when:
1. I run get_ignition_time() directly in: psql, pgadmin got the right values.
2. I run get_ignition_time() inside iReport preview and got the right values.
3. But, when i run the report from web server got wrong values...first i thought on timezone issues, because i have had issues with timestamp constraints at resotring database, but the timestamp mismatch the CST timezone, if you compare the time stamp from 1 step the difference is not 6hr.
the get_igniton_time is called in a cursor inside get_fuel_consumption but when i fetch it the ini_time and end_time are wrong like in 3rd step
I was rewrote the function many times,
i have used window value first_value and last_value,
i have wrote one query when igntion is on anohter whem igntion is off then joined,
etc,
i have wrote on different ways to get the same value like step 1 and 2 but 3 is always wrong...
i have set the order, like mentionend on 'windowedagg ... mail'
but no success ....
what is the safe way to use windowed function???
this is the last function i wrote:
CREATE OR REPLACE FUNCTION get_ignition_time(in punits character varying, pfrom character varying, pto character varying)
RETURNS TABLE(id_unit integer
, ini_time timestamp with time zone
, end_time timestamp with time zone) as
$BODY$
DECLARE
vunits integer[]= string_to_array(punits, ',');
BEGIN
RETURN QUERY with foo as (
select st.id_trun, st.time_stamp
, min(st.time_stamp) filter (where ignition=true) over w
, max(st.time_stamp) filter (where ignition=false) over w
from big_big_table st
where st.id_trun = ANY(ARRAY[vunits])
and st.time_stamp>=pfrom::timestamptz and st.time_stamp < pto::timestamptz
window w as (partition by st.id_trun )
order by st.id_trun,st.time_stamp
)
select distinct f.id_trun,f.min,f.max from foo f where min is not null and max is not null;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
CREATE OR REPLACE FUNCTION get_fuel_cosumption_dt(IN truns character varying, IN dfrom character varying, IN dto character varying)
RETURNS TABLE(
id_trun integer,
first_day smallint,
last_day smallint,
consumtpion_over_day bigint,
recharge_over_day bigint
) AS
$BODY$
DECLARE
rec record;
trip cursor for select * from hydra.get_ignition_time(truns,dfrom,dto);
BEGIN
create temp table if not exists t_fuel_consumption_dt(
id_trun integer,
first_day smallint,
last_day smallint,
cosumption_over_day bigint,
recharge_over_day bigint
) on commit drop;
open trip;
loop fetch trip into rec;
exit when not found;
raise log 'XXX::>> select r.* from hydra.rep_calculo_gas(''%'', ''%'', ''%'') r;' , rec.id_trun::varchar,rec.ini_time::varchar,rec.end_time::varchar ;
insert into t_fuel_consumption_dt
select
r.*
from hydra.get_consumption(rec.id_trun::varchar,rec.ini_time::varchar,rec.end_time::varchar) r;
end loop;
close trip;
return query select * from t_fuel_consumption_dt;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;
