PostgreSQL实现dateadd函数_计算机技术学习交流网

PostgreSQL实现dateadd函数

Sql代码

CREATE OR REPLACE FUNCTION timeadd(difftype character varying, incrementvalue integer, inputdate timestamp with time zone)

RETURNS timestamp without time zone AS

$BODY$ www.2cto.com

DECLARE

YEAR_CONST Char(15) := 'year';

MONTH_CONST Char(15) := 'month';

WEEK_CONST Char(15) := 'week';

DAY_CONST Char(15) := 'day';

HOUR_CONST Char(15) := 'hour';

MIN_CONST Char(15) :='mi';

SECOND_CONST Char(15) :='ss';

dateTemp timestamp;

intervals interval;

BEGIN

IF lower($1) = lower(YEAR_CONST) THEN

select cast(cast(incrementvalue as character varying) || ' year' as interval) into intervals; www.2cto.com

ELSEIF lower($1) = lower(MONTH_CONST) THEN

select cast(cast(incrementvalue as character varying) || ' months' as interval) into intervals;

ELSEIF lower($1) = lower(WEEK_CONST) THEN

select cast(cast(incrementvalue as character varying) || ' week' as interval) into intervals;

ELSEIF lower($1) = lower(DAY_CONST) THEN

select cast(cast(incrementvalue as character varying) || ' day' as interval) into intervals;

ELSEIF lower($1) = lower(HOUR_CONST) THEN

select cast(cast(incrementvalue as character varying) || ' hour' as interval) into intervals;

ELSEIF lower($1) = lower(MIN_CONST) THEN

select cast(cast(incrementvalue as character varying) || ' minute' as interval) into intervals;

ELSEIF lower($1) = lower(SECOND_CONST) THEN

select cast(cast(incrementvalue as character varying) || ' second' as interval) into intervals;

END IF;

dateTemp:= inputdate + intervals;

RETURN dateTemp;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE;

www.2cto.com

------------------------------------------- API ------------------------------------------------------------

功能：类似于Sybase语法中的dateadd函数，用于时间加减相应的时间段

PostgreSQL实现dateadd函数