PostgreSQL Oracle 兼容性之 - add_months

背景

PS - 当前PG orafce, GPDB orafunc已修复这个BUG

有网友反映PostgreSQL oraface的add_months在某些日期与Oracle 的add_months不一致。

查了一下Oracle 的开发手册，add_months是这样定义的， 如果当前日期是月末，或者目标月没有当前日期的，取最后一天。

例子

2015年2月28日是2月的最后一天，所以按照Oracle的计算方法，无论加减多少个月结果应该都是目标月份的月末，而PostgreSQL 并不是这样的 ：　　

postgres=# select timestamp '2015-02-28' - interval '1 month';  
      ?column?         
---------------------  
 2015-01-28 00:00:00  
(1 row)  
  
postgres=# select oracle.add_months('2015-02-28 11:11:11+08',-1);  
     add_months        
---------------------  
 2015-01-28 11:11:11  
(1 row)  

以上查询在Oracle应该得到1月31号的结果。

目标月份没有当前日期，去目标月份的最后一天，比如3月30日减去一个月，不可能是2月30日，所以取2月的最后一天，这个规则是和Oracle一致的。

postgres=# select timestamp '2015-03-30' - interval '1 month';  
      ?column?         
---------------------  
 2015-02-28 00:00:00  
(1 row)  

Oracle add_months的解释如下 ：

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions004.htm

ADD_MONTHS returns the date date plus integer months. The date argument can be a datetime value or any value that can be implicitly converted to DATE. The integer argument can be an integer or any value that can be implicitly converted to an integer. The return type is always DATE, regardless of the datatype of date. If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.  

orafce中add_months的代码

SELECT ($1 + interval '1 month' * $2)::oracle.date;    

问题就出在这里。

所以要和Oracle完全兼容，可以这样

创建两个这样的函数，如果当前日期是月末的话，则目标月取月末，否则就按照PG原来的算法。

create or replace function add_months(timestamp, int) returns timestamp as $$  
declare  
  i interval := ($2 || 'month');  
  d1 date := date(to_timestamp($1::text,'yyyy-mm') + interval '1 month' - interval '1 day');  
  d2 date := date($1);  
  res timestamp;  
begin  
  select case when d1=d2 then ((to_char($1+i+interval '1 month', 'yyyy-mm')||'-01')::date - 1) + $1::time else $1+i end into res;  
  return res;  
end;  
$$ language plpgsql strict;  
  
create or replace function add_months(timestamptz, int) returns timestamptz as $$  
declare  
  i interval := ($2 || 'month');  
  d1 date := date(to_timestamp($1::text,'yyyy-mm') + interval '1 month' - interval '1 day');  
  d2 date := date($1);  
  res timestamptz;  
begin  
  select case when d1=d2 then ((to_char($1+i+interval '1 month', 'yyyy-mm')||'-01')::date - 1) + $1::timetz else $1+i end into res;  
  return res;  
end;  
$$ language plpgsql strict;  

测试 ：　

达到目的

postgres=# select add_months('2015-02-28 11:11:11+08',-1);  
       add_months         
------------------------  
 2015-01-31 11:11:11+08  
(1 row)  
  
postgres=# select add_months('2015-02-28 11:11:11+08',-12);  
       add_months         
------------------------  
 2014-02-28 11:11:11+08  
(1 row)  
  
postgres=# select add_months('2015-02-28 11:11:11+08',-24);  
       add_months         
------------------------  
 2013-02-28 11:11:11+08  
(1 row)  
  
postgres=# select add_months('2015-02-28 11:11:11+08',-36);  
       add_months         
------------------------  
 2012-02-29 11:11:11+08  
(1 row)  
  
postgres=# select add_months('2015-03-30 11:11:11+08',-1);  
       add_months         
------------------------  
 2015-02-28 11:11:11+08  
(1 row)  
  
postgres=# select add_months('2015-03-31 11:11:11+08',-1);  
       add_months         
------------------------  
 2015-02-28 11:11:11+08  
(1 row)  
  
postgres=# select add_months('2015-03-31 11:11:11+08',1);  
       add_months         
------------------------  
 2015-04-30 11:11:11+08  
(1 row)  
  
postgres=# select add_months('2015-03-30 11:11:11+08',1);  
       add_months         
------------------------  
 2015-04-30 11:11:11+08  
(1 row)  
  
postgres=# select add_months('2015-02-28 11:11:11+08',1);  
       add_months         
------------------------  
 2015-03-31 11:11:11+08  
(1 row)  

digoal’s 大量PostgreSQL文章入口