術語

• 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