Re: Calculation error

Wil Duis <Wil.Duis@asml.com> writes:
> v_startmoment := timestamp(p_start_date,p_start_time);
> v_epoch_start := date_part(epoch, v_startmoment);
> v_stopmoment  := timestamp(p_stop_date,p_stop_time);
> v_epoch_start := date_part(epoch, v_stopmoment );

The function name "timestamp" needs to be double quoted here to avoid a
syntax conflict with the type declaration syntax TIMESTAMP(n).  But
actually I'd use timestamptz, assuming that your dates and times are in
local time --- the above calculation will give the wrong answers across
a daylight-savings transition.

BTW I think you meant to assign to v_epoch_stop in the last line quoted.
You should also consider making v_epoch_start and v_epoch_stop be float8
not integer, if you want the code to not break in 2038.

>   v_query := ''update int_performance_facts
>                 set duration = calc_duration
>                                ( start_date
>                                , start_time
>                                , stop_date
>                                , stop_time
>                                );
>               '';
>    execute v_query;

Seems like the hard way.  Why not just do the UPDATE directly?

            regards, tom lane