this issues could be raised by a incorrect jdbc? the jdbc used is postrgresql-9.2-1002.jdbc4.jar and i have a postgresql 9.4
On Wed, Feb 1, 2017 at 9:35 AM, Edmundo Robles <edmu...@sw-argos.com> wrote: > > 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; > > --