Oracle时间戳转换日期

1、Oracle时间戳（毫秒）转为Date

SELECT 1656991607001 / (1000 * 60 * 60 * 24) + TO_DATE('1970-01-01 08:00:00', 'yyyy-MM-dd hh24:mi:ss') CDATE  FROM dual;

2、以1970年时间作为基准格式化日期

select  TO_CHAR(TO_DATE (‘28-JUL-08’, ‘DD-MON-RR’, ‘NLS_DATE_LANGUAGE = American’) ,'YYYY-MM-DD') from dual;

3、字符型转成timestamp

select to_timestamp('2011-09-14 12:52:42.123456789', 'syyyy-mm-dd hh24:mi:ss.ff') from dual;

4、timestamp转成date型

select cast(to_timestamp('2011-09-14 12:52:42.123456789', 'syyyy-mm-dd hh24:mi:ss.ff') as date) timestamp_to_date from dual;

5、date型转成timestamp

select cast(sysdate as timestamp) date_to_timestamp from dual;

6、两date的日期相减得出的是天数，而两timestamp的日期相减得出的是完整的年月日时分秒小数秒

select sysdate-sysdate,systimestamp-systimestamp from dual;

转载自：https://blog.csdn.net/x15270772831/article/details/82985994