Oracle SQL 日期转换时间戳 时间戳转化日期

-- 日期转换为毫秒级时间戳  24*60*60 *1000
SELECT (to_date('2019/4/11 14:45:43'
               ,'yyyy-mm-dd hh24:mi:ss') - to_date('1970-1-1'
                                                   ,'yyyy-mm-dd')) * 86400000 -
       to_number(substr(tz_offset(sessiontimezone)
                       ,1
                       ,3)) * 3600000 AS convert_to_timestamp
  FROM dual;
 

-- 毫秒级时间戳 转日期  
SELECT to_date('19700101'
              ,'yyyymmdd') + 1554965143000 / 86400000 +
       to_number(substr(tz_offset(sessiontimezone)
                       ,1
                       ,3)) / 24 AS convert_to_date
  FROM dual;
 

-- 日期转换为秒级 时间戳  24*60*60
SELECT round((to_date('2019/4/11 14:45:43'
               ,'yyyy-mm-dd hh24:mi:ss') - to_date('1970-1-1'
                                                   ,'yyyy-mm-dd')) * 86400 -
       to_number(substr(tz_offset(sessiontimezone)
                       ,1
                       ,3)) * 3600) AS convert_to_timestamp
  FROM dual;
 
 

-- 秒级 时间戳  24*60*60 
SELECT to_date('19700101'
              ,'yyyymmdd') + 1554965143 / 86400 +
       to_number(substr(tz_offset(sessiontimezone)
                       ,1
                       ,3)) / 24 AS convert_to_date
  FROM dual;

-- 刘轶鹤