时间转换数字与日期互转

时间转换

再和java项目对接的时候，我们经常会遇到将数字类型与时间格式互相转化

1. 数字转日期

FUNCTION number_to_date(p_number IN NUMBER) RETURN DATE IS
    l_date DATE;
BEGIN
    SELECT to_date('1970/01/01 08:00:00'
                  ,'YYYY/MM/DD HH24:MI:SS') + (p_number / (24 * 60 * 60 * 1000))
    INTO   l_date
    FROM   dual;
    RETURN l_date;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END number_to_date;

2. 日期转数字

FUNCTION date_to_number(p_date IN DATE) RETURN NUMBER IS
    l_number NUMBER;
BEGIN
    SELECT (p_date - to_date('1970/01/01 08:00:00'
                            ,'YYYY/MM/DD HH24:MI:SS')) * 24 * 60 * 60 * 1000
    INTO   l_number
    FROM   dual;
    RETURN l_number;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END date_to_number;