Search Postgresql Archives

Another windowed function with different values.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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;


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux