PostgreSQL实现dateadd函数

housen1987

 
CREATE OR REPLACE FUNCTION timeadd(difftype character varying, incrementvalue integer, inputdate timestamp with time zone)  RETURNS timestamp without time zone AS$BODY$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;   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; 
------------------------------------------- API ------------------------------------------------------------
功能：类似于Sybase语法中的dateadd函数，用于时间加减相应的时间段
参数：

• $1 时间运算格式，分为：ss（秒）,mi（分钟）,hour（小时）,day（天）,week（周）,month（月）,year（年）
  
• $2 时间运算间隔，整型，正负均可
  
• $3 本地时间，常用now()代替
  

范例：

select timeadd('day',-1,now())