将Unix时间戳转换为Date和DateTime-SQL/ORACLE
问题描述：
我有一个NUMBER(15)数据类型的列，用于存储unix时间戳.我正在尝试从中获取日期和日期时间.我已经可以从中获取日期时间，但是无法获取日期.例如1456342438是我要转换为日期和日期时间的值.
I have a column of NUMBER(15) datatype which stores unix timestamps. I am trying to get a date and datetime from it. I have been able to get datetime from it but not a date. e.g. 1456342438 is the value i am trying to convert to date and datetime.
这些查询中的任何一个都给我一个DateTime:
Either of these queries give me a DateTime:
select TO_DATE('1970-01-01','YYYY-MM-DD') + numtodsinterval(1456342438,'SECOND') from dual;
select TO_DATE('19700101000000','YYYYMMDDHH24MISS') + numtodsinterval(1456342438,'SECOND') from dual;
如何从该值获取日期?
答
select to_date('01/01/1970','mm/dd/yyyy') + numtodsinterval(1456342438,'SECOND'),
TRUNC(to_date('01/01/1970','mm/dd/yyyy') + numtodsinterval(1456342438,'SECOND')) from dual;
这行得通.