查询一个列,列值如下:
SQL> select itime from tbl_url_20141117 where rownum < 10;
ITIME
----------
1416185504
1416197381
1416212311
1416191573
1416195525
1416197381
1416197381
1416198703
1416200879
经检查发现,发现这些值是以秒为单位的时间,需要将其进行转化,过程如下:
SQL> select to_char((itime/86400 +to_date('1970-01-01','yyyy-MM-dd')),'YYYY-MM-DD HH24:MI:SS') from tbl_url_20141117 where rownum < 10;
#如果是毫秒级的列值,则需要除以86400000
TO_CHAR((ITIME/8640
-------------------
2014-11-17 00:51:44
2014-11-17 04:09:41
2014-11-17 08:18:31
2014-11-17 02:32:53
2014-11-17 03:38:45
2014-11-17 04:09:41
2014-11-17 04:09:41
2014-11-17 04:31:43
2014-11-17 05:07:59