PostgreSQL Oracle 兼容性之 - add_months

本文探讨了PostgreSQL和Oracle在处理add_months函数时的不同表现,并提供了一种方法来使PostgreSQL的行为与Oracle保持一致。

有网友反映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)
--4、去月保费 Execute Immediate 'truncate table jsxs_day_qy1 '; insert into jsxs_day_qy1 select /*+parallel(4) */ level2comcode, level3comcode, riskcode, newchnltype, sum(PREMIUM) PREMIUM, datetype from (select /*+parallel(4) */ b.level2comcode, b.level3comcode, substr(a.RISKCODE, 1, 2) as riskcode, (case when x.newchnltype is null then '00' else x.newchnltype end) newchnltype, (a.PREMIUM * a.EXCHRATE * a.COINSRATE / 100) AS PREMIUM, --原始保费收入 'STATDATE' as datetype FROM WEB_LIST_CMAINORIGIN a, cd_com_all b, ods_cmain x WHERE a.STATDATE between add_months(trunc(sysdate - 1, 'mm'), -12) and add_months(trunc(sysdate - 1), -12) and a.COMCODE = b.comcode and a.policyno = x.policyno(+) UNION ALL select /*+parallel(4) */ d.level2comcode, d.level3comcode, substr(c.RISKCODE, 1, 2) as riskcode, (case when y.newchnltype is null then '00' else y.newchnltype end) newchnltype, (c.CHGPREMIUM * c.EXCHRATE * c.COINSRATE / 100) AS PREMIUM, --批改保费 'STATDATE' as datetype FROM WEB_LIST_PMAIN c, cd_com_all d, ods_pmain y WHERE c.STATDATE between add_months(trunc(sysdate - 1, 'mm'), -12) and add_months(trunc(sysdate - 1), -12) and c.COMCODE = d.comcode and c.endorseno = y.endorseno(+) and c.CHGPREMIUM != 0 union all select /*+parallel(4) */ e.level2comcode, e.level3comcode, substr(f.RISKCODE, 1, 2) as riskcode, (case when m.newchnltype is null then '00' else m.newchnltype end) newchnltype, (f.PREMIUM * f.EXCHRATE * f.COINSRATE / 100) AS PREMIUM, --原始保费收入 'UNDERWRITEENDDATE' as datetype FROM WEB_LIST_CMAINORIGIN f, cd_com_all e, ods_cmain m WHERE f.UNDERWRITEENDDATE between add_months(trunc(sysdate - 1, 'mm'), -12) and add_months(trunc(sysdate - 1), -12) and f.COMCODE = e.comcode and f.policyno = m.policyno(+) UNION ALL select /*+parallel(4) */ h.level2comcode, h.level3comcode, substr(g.RISKCODE, 1, 2) as riskcode, (case when n.newchnltype is null then '00' else n.newchnltype end) newchnltype, (g.CHGPREMIUM * g.EXCHRATE * g.COINSRATE / 100) AS PREMIUM, --批改保费 'UNDERWRITEENDDATE' as datetype FROM WEB_LIST_PMAIN g, cd_com_all h, ods_pmain n WHERE g.STATDATE between add_months(trunc(sysdate - 1, 'mm'), -12) and add_months(trunc(sysdate - 1), -12) and g.COMCODE = h.comcode and g.endorseno = n.endorseno(+) and g.CHGPREMIUM != 0) group by level2comcode, level3comcode, riskcode, newchnltype, datetype; --5、去年保费 Execute Immediate 'truncate table jsxs_day_qn1'; insert into jsxs_day_qn1 select /*+parallel(4) */ level2comcode, level3comcode, riskcode, newchnltype, sum(PREMIUM) PREMIUM, datetype from (select /*+parallel(4) */ b.level2comcode, b.level3comcode, substr(a.RISKCODE, 1, 2) as riskcode, (case when x.newchnltype is null then '00' else x.newchnltype end) newchnltype, (a.PREMIUM * a.EXCHRATE * a.COINSRATE / 100) AS PREMIUM, --原始保费收入 'STATDATE' as datetype FROM WEB_LIST_CMAINORIGIN a, cd_com_all b, ods_cmain x WHERE a.STATDATE between add_months(trunc(sysdate - 1, 'yy'), -12) and add_months(trunc(sysdate - 1), -12) and a.COMCODE = b.comcode and a.policyno = x.policyno(+) UNION ALL select /*+parallel(4) */ d.level2comcode, d.level3comcode, substr(c.RISKCODE, 1, 2) as riskcode, (case when y.newchnltype is null then '00' else y.newchnltype end) newchnltype, (c.CHGPREMIUM * c.EXCHRATE * c.COINSRATE / 100) AS PREMIUM, --批改保费 'STATDATE' as datetype FROM WEB_LIST_PMAIN c, cd_com_all d, ods_pmain y WHERE c.STATDATE between add_months(trunc(sysdate - 1, 'yy'), -12) and add_months(trunc(sysdate - 1), -12) and c.COMCODE = d.comcode and c.endorseno = y.endorseno(+) and c.CHGPREMIUM != 0 union all select /*+parallel(4) */ e.level2comcode, e.level3comcode, substr(f.RISKCODE, 1, 2) as riskcode, (case when m.newchnltype is null then '00' else m.newchnltype end) newchnltype, (f.PREMIUM * f.EXCHRATE * f.COINSRATE / 100) AS PREMIUM, --原始保费收入 'UNDERWRITEENDDATE' as datetype FROM WEB_LIST_CMAINORIGIN f, cd_com_all e, ods_cmain m WHERE f.UNDERWRITEENDDATE between add_months(trunc(sysdate - 1, 'yy'), -12) and add_months(trunc(sysdate - 1), -12) and f.COMCODE = e.comcode and f.policyno = m.policyno(+) UNION ALL select /*+parallel(4) */ h.level2comcode, h.level3comcode, substr(g.RISKCODE, 1, 2) as riskcode, (case when n.newchnltype is null then '00' else n.newchnltype end) newchnltype, (g.CHGPREMIUM * g.EXCHRATE * g.COINSRATE / 100) AS PREMIUM, --批改保费 'UNDERWRITEENDDATE' as datetype FROM WEB_LIST_PMAIN g, cd_com_all h, ods_pmain n WHERE g.STATDATE between add_months(trunc(sysdate - 1, 'yy'), -12) and add_months(trunc(sysdate - 1), -12) and g.COMCODE = h.comcode and g.endorseno = n.endorseno(+) and g.CHGPREMIUM != 0) group by level2comcode, level3comcode, riskcode, newchnltype, datetype; --6、去季度保费 Execute Immediate 'truncate table jsxs_day_qj1 '; insert into jsxs_day_qj1 select /*+parallel(4) */ level2comcode, level3comcode, riskcode, newchnltype, sum(PREMIUM) PREMIUM, datetype from (select /*+parallel(4) */ b.level2comcode, b.level3comcode, substr(a.RISKCODE, 1, 2) as riskcode, (case when x.newchnltype is null then '00' else x.newchnltype end) newchnltype, (a.PREMIUM * a.EXCHRATE * a.COINSRATE / 100) AS PREMIUM, --原始保费收入 'STATDATE' as datetype FROM WEB_LIST_CMAINORIGIN a, cd_com_all b, ods_cmain x WHERE a.STATDATE between add_months(trunc(sysdate - 1, 'q'), -12) and add_months(trunc(sysdate - 1), -12) and a.COMCODE = b.comcode and a.policyno = x.policyno(+) UNION ALL select /*+parallel(4) */ d.level2comcode, d.level3comcode, substr(c.RISKCODE, 1, 2) as riskcode, (case when y.newchnltype is null then '00' else y.newchnltype end) newchnltype, (c.CHGPREMIUM * c.EXCHRATE * c.COINSRATE / 100) AS PREMIUM, --批改保费 'STATDATE' as datetype FROM WEB_LIST_PMAIN c, cd_com_all d, ods_pmain y WHERE c.STATDATE between add_months(trunc(sysdate - 1, 'q'), -12) and add_months(trunc(sysdate - 1), -12) and c.COMCODE = d.comcode and c.endorseno = y.endorseno(+) and c.CHGPREMIUM != 0 union all select /*+parallel(4) */ e.level2comcode, e.level3comcode, substr(f.RISKCODE, 1, 2) as riskcode, (case when m.newchnltype is null then '00' else m.newchnltype end) newchnltype, (f.PREMIUM * f.EXCHRATE * f.COINSRATE / 100) AS PREMIUM, --原始保费收入 'UNDERWRITEENDDATE' as datetype FROM WEB_LIST_CMAINORIGIN f, cd_com_all e, ods_cmain m WHERE f.UNDERWRITEENDDATE between add_months(trunc(sysdate - 1, 'q'), -12) and add_months(trunc(sysdate - 1), -12) and f.COMCODE = e.comcode and f.policyno = m.policyno(+) UNION ALL select /*+parallel(4) */ h.level2comcode, h.level3comcode, substr(g.RISKCODE, 1, 2) as riskcode, (case when n.newchnltype is null then '00' else n.newchnltype end) newchnltype, (g.CHGPREMIUM * g.EXCHRATE * g.COINSRATE / 100) AS PREMIUM, --批改保费 'UNDERWRITEENDDATE' as datetype FROM WEB_LIST_PMAIN g, cd_com_all h, ods_pmain n WHERE g.STATDATE between add_months(trunc(sysdate - 1, 'q'), -12) and add_months(trunc(sysdate - 1), -12) and g.COMCODE = h.comcode and g.endorseno = n.endorseno(+) and g.CHGPREMIUM != 0) group by level2comcode, level3comcode, riskcode, newchnltype, datetype; --7、当季保费 Execute Immediate 'truncate table jsxs_day_dj1 '; insert into jsxs_day_dj1 select /*+parallel(4) */ level2comcode, level3comcode, riskcode, newchnltype, sum(PREMIUM) PREMIUM, datetype from (select /*+parallel(4) */ b.level2comcode, b.level3comcode, substr(a.RISKCODE, 1, 2) as riskcode, (case when x.newchnltype is null then '00' else x.newchnltype end) newchnltype, (a.PREMIUM * a.EXCHRATE * a.COINSRATE / 100) AS PREMIUM, --原始保费收入 'STATDATE' as datetype FROM WEB_LIST_CMAINORIGIN a, cd_com_all b, ods_cmain x WHERE a.STATDATE between trunc(trunc(sysdate - 1), 'q') and trunc(sysdate - 1) and a.COMCODE = b.comcode and a.policyno = x.policyno(+) UNION ALL select /*+parallel(4) */ d.level2comcode, d.level3comcode, substr(c.RISKCODE, 1, 2) as riskcode, (case when y.newchnltype is null then '00' else y.newchnltype end) newchnltype, (c.CHGPREMIUM * c.EXCHRATE * c.COINSRATE / 100) AS PREMIUM, --批改保费 'STATDATE' as datetype FROM WEB_LIST_PMAIN c, cd_com_all d, ods_pmain y WHERE c.STATDATE between trunc(trunc(sysdate - 1), 'q') and trunc(sysdate - 1) and c.COMCODE = d.comcode and c.endorseno = y.endorseno(+) and c.CHGPREMIUM != 0 union all select /*+parallel(4) */ e.level2comcode, e.level3comcode, substr(f.RISKCODE, 1, 2) as riskcode, (case when m.newchnltype is null then '00' else m.newchnltype end) newchnltype, (f.PREMIUM * f.EXCHRATE * f.COINSRATE / 100) AS PREMIUM, --原始保费收入 'UNDERWRITEENDDATE' as datetype FROM WEB_LIST_CMAINORIGIN f, cd_com_all e, ods_cmain m WHERE f.UNDERWRITEENDDATE between trunc(trunc(sysdate - 1), 'q') and trunc(sysdate - 1) and f.COMCODE = e.comcode and f.policyno = m.policyno(+) UNION ALL select /*+parallel(4) */ h.level2comcode, h.level3comcode, substr(g.RISKCODE, 1, 2) as riskcode, (case when n.newchnltype is null then '00' else n.newchnltype end) newchnltype, (g.CHGPREMIUM * g.EXCHRATE * g.COINSRATE / 100) AS PREMIUM, --批改保费 'UNDERWRITEENDDATE' as datetype FROM WEB_LIST_PMAIN g, cd_com_all h, ods_pmain n WHERE g.STATDATE between trunc(trunc(sysdate - 1), 'q') and trunc(sysdate - 1) and g.COMCODE = h.comcode and g.endorseno = n.endorseno(+) and g.CHGPREMIUM != 0) group by level2comcode, level3comcode, riskcode, newchnltype, datetype; --8、去年全年保费 Execute Immediate 'truncate table jsxs_day_qqn'; insert into jsxs_day_qqn select /*+parallel(4) */ level2comcode, level3comcode, riskcode, newchnltype, sum(PREMIUM) PREMIUM, datetype from (select /*+parallel(4) */ b.level2comcode, b.level3comcode, substr(a.RISKCODE, 1, 2) as riskcode, (case when x.newchnltype is null then '00' else x.newchnltype end) newchnltype, (a.PREMIUM * a.EXCHRATE * a.COINSRATE / 100) AS PREMIUM, --原始保费收入 'STATDATE' as datetype FROM WEB_LIST_CMAINORIGIN a, cd_com_all b, ods_cmain x WHERE a.STATDATE between add_months(trunc(sysdate - 1, 'yy'), -12) and trunc(sysdate - 1, 'yy') - 1 and a.COMCODE = b.comcode and a.policyno = x.policyno(+) UNION ALL select /*+parallel(4) */ d.level2comcode, d.level3comcode, substr(c.RISKCODE, 1, 2) as riskcode, (case when y.newchnltype is null then '00' else y.newchnltype end) newchnltype, (c.CHGPREMIUM * c.EXCHRATE * c.COINSRATE / 100) AS PREMIUM, --批改保费 'STATDATE' as datetype FROM WEB_LIST_PMAIN c, cd_com_all d, ods_pmain y WHERE c.STATDATE between add_months(trunc(sysdate - 1, 'yy'), -12) and trunc(sysdate - 1, 'yy') - 1 and c.COMCODE = d.comcode and c.endorseno = y.endorseno(+) and c.CHGPREMIUM != 0 union all select /*+parallel(4) */ e.level2comcode, e.level3comcode, substr(f.RISKCODE, 1, 2) as riskcode, (case when m.newchnltype is null then '00' else m.newchnltype end) newchnltype, (f.PREMIUM * f.EXCHRATE * f.COINSRATE / 100) AS PREMIUM, --原始保费收入 'UNDERWRITEENDDATE' as datetype FROM WEB_LIST_CMAINORIGIN f, cd_com_all e, ods_cmain m WHERE f.UNDERWRITEENDDATE between add_months(trunc(sysdate - 1, 'yy'), -12) and trunc(sysdate - 1, 'yy') - 1 and f.COMCODE = e.comcode and f.policyno = m.policyno(+) UNION ALL select /*+parallel(4) */ h.level2comcode, h.level3comcode, substr(g.RISKCODE, 1, 2) as riskcode, (case when n.newchnltype is null then '00' else n.newchnltype end) newchnltype, (g.CHGPREMIUM * g.EXCHRATE * g.COINSRATE / 100) AS PREMIUM, --批改保费 'UNDERWRITEENDDATE' as datetype FROM WEB_LIST_PMAIN g, cd_com_all h, ods_pmain n WHERE g.STATDATE between add_months(trunc(sysdate - 1, 'yy'), -12) and trunc(sysdate - 1, 'yy') - 1 and g.COMCODE = h.comcode and g.endorseno = n.endorseno(+) and g.CHGPREMIUM != 0) group by level2comcode, level3comcode, riskcode, newchnltype, datetype; --9、前年全年保费 Execute Immediate 'truncate table jsxs_day_qiannianquan'; insert into jsxs_day_qiannianquan select /*+parallel(4) */ level2comcode, level3comcode, riskcode, newchnltype, sum(PREMIUM) PREMIUM, datetype from (select /*+parallel(4) */ b.level2comcode, b.level3comcode, substr(a.RISKCODE, 1, 2) as riskcode, (case when x.newchnltype is null then '00' else x.newchnltype end) newchnltype, (a.PREMIUM * a.EXCHRATE * a.COINSRATE / 100) AS PREMIUM, --原始保费收入 'STATDATE' as datetype FROM WEB_LIST_CMAINORIGIN a, cd_com_all b, ods_cmain x WHERE a.STATDATE between add_months(trunc(sysdate - 1, 'yy'), -24) and add_months(trunc(sysdate - 1, 'yy'), -12) - 1 and a.COMCODE = b.comcode and a.policyno = x.policyno(+) UNION ALL select /*+parallel(4) */ d.level2comcode, d.level3comcode, substr(c.RISKCODE, 1, 2) as riskcode, (case when y.newchnltype is null then '00' else y.newchnltype end) newchnltype, (c.CHGPREMIUM * c.EXCHRATE * c.COINSRATE / 100) AS PREMIUM, --批改保费 'STATDATE' as datetype FROM WEB_LIST_PMAIN c, cd_com_all d, ods_pmain y WHERE c.STATDATE between add_months(trunc(sysdate - 1, 'yy'), -24) and add_months(trunc(sysdate - 1, 'yy'), -12) - 1 and c.COMCODE = d.comcode and c.endorseno = y.endorseno(+) and c.CHGPREMIUM != 0 union all select /*+parallel(4) */ e.level2comcode, e.level3comcode, substr(f.RISKCODE, 1, 2) as riskcode, (case when m.newchnltype is null then '00' else m.newchnltype end) newchnltype, (f.PREMIUM * f.EXCHRATE * f.COINSRATE / 100) AS PREMIUM, --原始保费收入 'UNDERWRITEENDDATE' as datetype FROM WEB_LIST_CMAINORIGIN f, cd_com_all e, ods_cmain m WHERE f.UNDERWRITEENDDATE between add_months(trunc(sysdate - 1, 'yy'), -24) and add_months(trunc(sysdate - 1, 'yy'), -12) - 1 and f.COMCODE = e.comcode and f.policyno = m.policyno(+) UNION ALL select /*+parallel(4) */ h.level2comcode, h.level3comcode, substr(g.RISKCODE, 1, 2) as riskcode, (case when n.newchnltype is null then '00' else n.newchnltype end) newchnltype, (g.CHGPREMIUM * g.EXCHRATE * g.COINSRATE / 100) AS PREMIUM, --批改保费 'UNDERWRITEENDDATE' as datetype FROM WEB_LIST_PMAIN g, cd_com_all h, ods_pmain n WHERE g.STATDATE between add_months(trunc(sysdate - 1, 'yy'), -24) and add_months(trunc(sysdate - 1, 'yy'), -12) - 1 and g.COMCODE = h.comcode and g.endorseno = n.endorseno(+) and g.CHGPREMIUM != 0) group by level2comcode, level3comcode, riskcode, newchnltype, datetype;】改为高斯DB
08-09
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值