Re: Another windowed function with different values.

i just changed the jdbc, but still get the wrong values...

On Wed, Feb 1, 2017 at 9:47 AM, Edmundo Robles <edmundo(at)sw-argos(dot)com> wrote:

> 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 <edmundo(at)sw-argos(dot)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;
>>
>>
>
>
> --
>
>

--