术语

• ApsaraDB for RDS(PPAS)，以下简称PPAS

PPAS与Oracle的日期计算结果对比

PPAS：

ppas=# SELECT TO_DATE('20140319 121212','yyyymmdd hh24miss') - TO_DATE('20140522 232323','yyyymmdd hh24miss') FROM dual;
      ?column?      
--------------------
 -64 days -11:11:11
(1 row)

Oracle：

SQL> SELECT TO_DATE('20140319 121212','yyyymmdd hh24miss') - TO_DATE('20140522 232323','yyyymmdd hh24miss') FROM dual;

TO_DATE('20140319121212','YYYYMMDDHH24MISS')-TO_DATE('20140522232323','YYYYMMDDH
--------------------------------------------------------------------------------
                                    -64.4661

解决方案

ppas=# CREATE OR REPLACE FUNCTION interval2number (
    p1    INTERVAL
) RETURN NUMBER
IS
BEGIN
    RETURN EXTRACT(EPOCH FROM p1 / 60 / 24) /60 ;
END;

ppas=# SELECT interval2number(TO_DATE('20140319 121212','yyyymmdd hh24miss') - TO_DATE('20140522 232323','yyyymmdd hh24miss')) FROM dual;
interval2number
--------------------------------------------------------------------------------
                                    -64.4660995333333