当前位置:数据库 > SQLServer >>

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.zzzyk.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.zzzyk.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.zzzyk.com  
------------------------------------------- API ------------------------------------------------------------
功能:类似于Sybase语法中的dateadd函数,用于时间加减相应的时间段
参数:
 
$1 时间运算格式,分为:ss(秒),mi(分钟),hour(小时),day(天),week(周),month(月),year(年)
$2 时间运算间隔,整型,正负均可
$3 本地时间,常用now()代替
 
范例:
Sql代码  
select timeadd('day',-1,now())  
 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,