Re: [GENERAL] Another windowed function with different values. - Mailing list pgsql-general

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

On Wed, Feb 1, 2017 at 9:47 AM, Edmundo Robles <edmundo@sw-argos.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@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;

--

--