FW:Oracle之PL/SQL 时间转换

CREATE OR REPLACE FUNCTION TTD(V_DATE NUMBER) RETURN VARCHAR2 IS
  RESULT VARCHAR(100);
BEGIN

  SELECT TO_CHAR(TO_DATE(‘1970-1-1 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) +
                 V_DATE / 3600 / 24 + 8 / 24,
                 ‘yyyy-mm-dd hh24:mi‘)
    INTO RESULT
    FROM DUAL;

  RETURN(RESULT);
END TTD;

CREATE OR REPLACE FUNCTION TTN(V_DATE VARCHAR2) RETURN NUMBER IS
  RESULT   NUMBER;
  TMP_DATE VARCHAR2(100);
BEGIN

  SELECT SUBSTR(V_DATE, 0, 4) || ‘-‘ || SUBSTR(V_DATE, 5, 2) || ‘-‘ ||
         SUBSTR(V_DATE, 7, 2) || ‘ ‘ || SUBSTR(V_DATE, 9, 2) || ‘:00:00 ‘
    INTO TMP_DATE
    FROM DUAL;

  SELECT (TO_DATE(TMP_DATE, ‘yyyy-mm-dd hh24:mi:ss‘) -
         TO_DATE(‘1970-01-01 08:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘)) * 86400
    INTO RESULT
    FROM DUAL;

  RETURN(RESULT);
END TTN;

-- 日期转数字
SELECT TTN(‘2014071408‘) FROM DUAL;
-- 数字转日期
SELECT TTD(1405296000) FROM DUAL;