[PostgreSQL 8.2] 사용자 정의 함수 DateDiff 만들기 (Create DateDiff Function Like SQL Server)

* 문제: Greenplum에서 DateDiff 함수가 존재하지 않을 경우

* 해결: 간단한 사용자 정의 함수를 만들어 사용한다.

사용자 정의 함수 생성

CREATE OR REPLACE FUNCTION simple_datediff(difftype character varying, date1 date, date2 date)
 RETURNS integer AS
$BODY$
DECLARE
 DateValue Double Precision := 0;
 ReturnValue Integer := 0;
 flag Integer := 1 ;
 af_date date;
 bf_date date;
BEGIN 
 IF date2 < date1 THEN 
  flag := -1 ;
  af_date := date1; 
  bf_date := date2;
 ELSE 
  af_date := date2; 
  bf_date := date1;  
 END IF;
  
 IF lower($1) IN ('yy','yyyy','year') THEN
  DateValue := EXTRACT(YEAR FROM af_date) - EXTRACT(YEAR FROM bf_date);

 ELSEIF lower($1) IN ('m','mm','month') THEN
  DateValue := (EXTRACT(YEAR FROM af_date) - EXTRACT(YEAR FROM bf_date))*12 + (EXTRACT(month FROM af_date) - EXTRACT(month FROM bf_date));

 ELSEIF lower($1) IN ('wk','ww','week') THEN
  DateValue := TRUNC((EXTRACT(day from (af_date::timestamp - bf_date::timestamp)) + EXTRACT(dow from (bf_date::timestamp)))/7);

 ELSEIF lower($1) IN ('d','dd','day') THEN
  DateValue := af_date - bf_date;
 END IF;
 
 ReturnValue := CAST(DateValue AS Integer) * flag;
 
 RETURN ReturnValue;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE;

함수 사용

select simple_datediff('year','2014-12-31','2015-02-01')
select simple_datediff('month','2014-12-31','2015-02-01')
select simple_datediff('week','2014-12-31','2015-01-01')
select simple_datediff('week','2015-01-31','2015-02-01')
select simple_datediff('day','2014-12-31','2015-02-01')