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;
 

-- 刘轶鹤