pg>db2>timestampdiff
2021-09-26 13:09:22    101    0    0
ivan

CREATE OR REPLACE FUNCTION db2_timestampdiff(i_type int, tp1 timestamp with time zone, tp2 timestamp with time zone)

RETURNS numeric

LANGUAGE plpgsql

AS $function$

    DECLARE

        l_result number;

        t_result number;

    begin

        l_result:=null;

        --秒

        if ( i_type = 2 ) then

            select floor(extract(epoch from (tp1-tp2))) into t_result;

            l_result := t_result;

        end if;

        --分钟

        if (i_type = 4) then

            select floor(extract(epoch from (tp1-tp2))/60) into t_result;

            l_result := t_result;

        end if;

        --小时

        if (i_type = 8) then

            select floor(extract(epoch from (tp1-tp2))/60/60) into t_result;

            l_result := t_result;

        end if;

        --天

        if (i_type = 16) then

            select floor(extract(day from (tp1-tp2))) into t_result;

            l_result := t_result;

        END IF;

        --周

        if (i_type = 32) then

            select floor(extract(WEEKDAY from (tp1-tp2))) into t_result;

            l_result := t_result;

        END IF;

        --月

        if (i_type = 64) then

            select floor(extract(MONTH from (tp1-tp2))) into t_result;

            l_result := t_result;

        END IF;

        --年

        if (i_type = 256) then

            select floor(extract(YEAR from (tp1-tp2))) into t_result;

            l_result := t_result;

        END IF;

        return l_result;

    end;

$function$

 

 

Pre: pg>generate_series产生连续数据

Next: pg>游标及客户端查询

101
Sign in to leave a comment.
No Leanote account? Sign up now.
0 comments
Table of content