oracle日期、时间计算与转换集合

--oracle两时间相减得到相差的时间

--1.months_between(date1,date2);date1和date2相减得到相差的月份。
   
   SELECT MONTHS_BETWEEN(TO_DATE('2015-05-11', 'yyyy-MM-dd'),
                         TO_DATE('2015-04-11', 'yyyy-MM-dd'))
     FROM DUAL;--相差一个月。

--2.ceil(date1-date2);date1-date2相减得到相差的天数
   
   SELECT CEIL(TO_DATE('2015-05-11 00:00:00', 'yyyy-mm-dd hh24-mi-ss') -
               TO_DATE('2015-04-11 00:00:00', 'yyyy-mm-dd hh24-mi-ss'))
     FROM DUAL; --相差30天。

--3.获取两时间相差的豪秒数 ceil((date1 - date2) * 24 * 60 * 60 * 1000)
  
  SELECT CEIL((TO_DATE('2015-05-11 00:00:00', 'yyyy-mm-dd hh24-mi-ss') -
              TO_DATE('2015-04-11 23:59:59', 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60 * 1000)
    FROM DUAL;

--4.获取两时间相差的秒数 ceil((date1 - date2) * 24 * 60 * 60 )
SELECT CEIL((TO_DATE('2015-05-11 00:00:00', 'yyyy-mm-dd hh24-mi-ss') -
            TO_DATE('2015-04-11 23:59:59', 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60)
  FROM DUAL;

--5.获取两时间相差的分钟数 ceil((date1 - date2) * 24 * 60 )
    
    SELECT CEIL((TO_DATE('2015-05-11 00:00:00', 'yyyy-mm-dd hh24-mi-ss') -
                TO_DATE('2015-04-11 23:59:59', 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60)
      FROM DUAL;

--6.获取两时间相差小时数 ceil((date1 - date2) * 24 )
 
 SELECT CEIL((TO_DATE('2015-05-11 00:00:00', 'yyyy-mm-dd hh24-mi-ss') -
             TO_DATE('2015-04-11 23:59:59', 'yyyy-mm-dd hh24-mi-ss')) * 24)
   FROM DUAL;

--7.其他
   
   SELECT SYSDATE, ADD_MONTHS(SYSDATE, 12) FROM DUAL; --加1年
   
   SELECT SYSDATE, ADD_MONTHS(SYSDATE, -12) FROM DUAL; --减1年
   
   SELECT SYSDATE, TO_CHAR(SYSDATE + 1, 'yyyy-mm-dd HH24:MI:SS') FROM DUAL; --加1天
   
   SELECT SYSDATE, TO_CHAR(SYSDATE + 1 / 24, 'yyyy-mm-dd HH24:MI:SS')
     FROM DUAL; --加1小时
   
   SELECT SYSDATE, TO_CHAR(SYSDATE + 1 / 24 / 60, 'yyyy-mm-dd HH23:MI:SS')
     FROM DUAL; --加1分钟
  
  SELECT SYSDATE,
         TO_CHAR(SYSDATE + 1 / 24 / 60 / 60, 'yyyy-mm-dd HH23:MI:SS')
    FROM DUAL; --加1秒
 

 

--毫秒转换日期
SELECT TO_CHAR(1506283724000 / (1000 * 60 * 60 * 24) +      
        TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH:MI:SS'), 'YYYY-MM-DD HH:MI:SS') AS CDATE      
 FROM DUAL;
1470886843400--2016-08-11 11:40:43    
1506416357412--2017-09-26 04:59:17
1506326924463--2017-09-25 04:08:44
1506076599125--2017-09-22 06:36:39
--日期转换毫秒
SELECT TO_NUMBER(TO_DATE('2017-09-25 04:08:44', 'YYYY-MM-DD HH24:MI:SS') -      
                 TO_DATE('1970-01-01 8:0:0', 'YYYY-MM-DD HH24:MI:SS')) * 24 * 60 * 60 * 1000      
  FROM DUAL;   
  

 

oracle中日期类型与unix 时间戳的转换
Unix时间戳记是从'1970-01-01 00:00:00'GMT开始的秒数，表现为整数型。
Oracle中的时间是Date型，以下函数提供了两种时间转换的Oracle函数

CREATE OR REPLACE FUNCTION UNIX_TO_ORACLE(IN_NUMBER NUMBER)
  RETURN DATE IS BEGIN
  RETURN(TO_DATE('19700101', 'yyyymmdd') + IN_NUMBER / 86400 +
         TO_NUMBER(SUBSTR(TZ_OFFSET(SESSIONTIMEZONE), 1, 3)) / 24);
END UNIX_TO_ORACLE;

CREATE OR REPLACE FUNCTION ORACLE_TO_UNIX(IN_DATE IN DATE) RETURN NUMBER IS
BEGIN
  RETURN((IN_DATE - TO_DATE('19700101', 'yyyymmdd')) * 86400 -
         TO_NUMBER(SUBSTR(TZ_OFFSET(SESSIONTIMEZONE), 1, 3)) * 3600);
END ORACLE_TO_UNIX;

SELECT oracle_to_unix(SYSDATE) FROM dual;
SELECT UNIX_TO_ORACLE(1506418954) FROM dual;