Re: Another windowed function with different values.

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;
>
>

--