oracle实战技巧（七）oracle时间戳与时间互相转换函数

时间戳转时间

  

   

    

     

    
    

     

      CREATE OR REPLACE
     
    
   

    

     

    
    

     

      FUNCTION tamp_to_date (i_timestamp IN NUMBER ) return DATE IS r_date DATE;
     
    
   

    

     

    
    

     

      BEGIN
     
    
   

    

     

    
    

     

      SELECT
     
    
   

    

     

    
    

     

      	i_timestamp/(1000*60*60*24) + to_date('1970-01-01 08:00:00','yyyy/mm/dd hh:mi:ss') into r_date from dual;
     
    
   

    

     

    
    

     

      return r_date;
     
    
   

    

     

    
    

     

      END tamp_to_date;
     
    
  
 

使用

  

   

    

     

    
    

     

       INSERT INTO earlywarning_programme (CREATE_TIME )
     
    
   

    

     

    
    

     

       VALUES
     
    
   

    

     

    
    

     

      	(tamp_to_date (#{CREATETIME}))
     
    
  
 

时间转时间戳

  

   

    

     

    
    

     

      CREATE OR REPLACE
     
    
   

    

     

    
    

     

      FUNCTION date_to_tamp (i_date IN DATE ) return NUMBER IS r_timestamp NUMBER;
     
    
   

    

     

    
    

     

      BEGIN
     
    
   

    

     

    
    

     

      SELECT
     
    
   

    

     

    
    

     

      	((i_date-to_date('1970-01-01','YYYY-MM-DD')) * 86400 - 8*3600)*1000 into r_timestamp from dual; 
     
    
   

    

     

    
    

     

      return r_timestamp;
     
    
   

    

     

    
    

     

      END date_to_tamp;
     
    
  
 

使用

 date_to_tamp(create_time) as create_time,
 

文章来源: baocl.blog.csdn.net，作者：小黄鸡1992，版权归原作者所有，如需转载，请联系作者。

原文链接：baocl.blog.csdn.net/article/details/105660370