各种sql时间戳和普通时间互换

==============
MYSQL:

  /*时间戳转换为日期2009年08月06  12:08:00*/
select FROM_UNIXTIME( 1249488000,'%Y年%m月%d  %h:%m:%s'); 


 /*日期转换为时间戳1249488000*/
SELECT UNIX_TIMESTAMP('2009-08-06 00:00:00') ;  


================

SQL Server:
例如：
   --时间戳转换成普通时间
   SELECT DATEADD(S,1160701488 + 8 * 3600,'1970-01-01 00:00:00')  
             
   --普通时间转换成时间戳
   SELECT DATEDIFF(S,'1970-01-01 00:00:00', '2006-10-13 09:04:48.000') - 8 * 3600      







========================================================
oracle中时间戳的算法
 

获取时间戳：

create or replace function getTimeStamp return integer is
  Result integer;
begin
  SELECT (SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24miss')) * 86400000
    + EXTRACT(SECOND FROM SYSTIMESTAMP(3)) * 1000
 into result FROM DUAL;

  return(Result);
end getTimeStamp;

 

时间戳变化为日期格式：

create or replace function getDateFromTimeStamp(tsp in integer) return date is
  Result date;
  tt     integer;
begin
  tt := substr(tsp, 0, 13);
  SELECT ((tt - EXTRACT(SECOND FROM SYSTIMESTAMP(3)) * 1000) / 86400000 +
         TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24miss'))
    into result
    FROM DUAL;
  return(Result);
end getDateFromTimeStamp;