Re: Another windowed function with different values.

this issue was raised by timezone settings ...

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

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

--