oracle数据库时间戳转换成日期时间方法

时间戳是自 1970 年 1 月 1 日（00:00:00 GMT）以来的秒数。它也被称为 Unix 时间戳（Unix Timestamp）。

Unix时间戳(Unix timestamp)，或称Unix时间(Unix time)、POSIX时间(POSIX time)，是一种时间表示方式，

定义为从格林威治时间1970年01月01日00时00分00秒起至现在的总秒数。

Unix时间戳不仅被使用在Unix系统、类Unix系统中，也在许多其他操作系统中被广泛采用。

北京时间是东8区，所以要加8小时。

1， 现在需要将oracle数据库中的表中的时间戳字段转换成北京时间的日期时间格式，表格如下图。

       COMPANY_NAME    BILL_NUM    STARTTIME    
1    随州市甜蜜食品厂    9002254531    1370769194    

使用下面方法可以转换。

SELECT c.company_name,c.bill_num,c.starttime,TO_CHAR(c.starttime / (60 * 60 * 24) +
               TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH:MI:SS'),
               'YYYY-MM-DD HH24:MI:SS') AS DAY_time
  FROM te_company c

下面是转换的结果图：

       COMPANY_NAME    BILL_NUM    STARTTIME       DAY_time
1    随州市甜蜜食品厂    9002254531    1370769194    2013-06-09 17:13:14

2，利用自建函数把时间戳转换成北京日期。

创建转换函数FUN_UTC2STRING：

create or replace function FUN_UTC2STRING(UTCDATE NUMBER, PATTERN VARCHAR2)
  return varchar2 is
  Result varchar2(19);
begin
  Result := TO_CHAR(TO_DATE(19700101000000, 'YYYYMMDDHH24MISS') +
                    (UTCDATE + 8 * 3600) / 86400,
                    PATTERN);
  return(Result);
end FUN_UTC2STRING;

使用PLSQL调用函数，转换时间戳成北京时间。

declare
  v_date    varchar2(19);
  v_utc     number;
  v_pattern varchar2(21);               --这个用于日期格式所以占符大些
begin
  v_utc     := 1504677363;              --日间戳
  v_pattern := 'YYYY-MM-DD HH24:MI:SS'; --日期格式
  v_date    := fun_utc2string(v_utc, v_pattern);
  dbms_output.put_line('UTC时间戳: '|| v_utc || ' 转换成北京时间是:' || v_date);
end;

UTC时间戳: 1504677363 转换成北京时间是:2017-09-06 13:56:03

当然也可以直接使用select 查询：

SQL> select e.bill_num,fun_utc2string(e.starttime,'yyyy-mm-dd hh24:mi:ss') as bjDATE from te_equip e where rownum<10;

BILL_NUM                         BJDATE
-------------------------------- --------------------------------------------------------------------------------
9000776870                       2012-03-20 15:15:21
9000776870                       2012-03-20 13:11:31
9000776870                       2012-03-20 13:13:39
9000776870                       2012-03-20 13:15:00
9000776870                       2012-03-20 13:31:40
9000776870                       2012-03-20 18:16:39
9000776870                       2012-03-20 17:30:07
9000776870                       2012-03-20 17:33:37
9000776870                       2012-03-20 13:48:19
9 rows selected