将Unix时间戳转换为Date和DateTime-SQL/ORACLE [英] Convert unix timestamp to Date and 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;

这行得通.

这篇关于将Unix时间戳转换为Date和DateTime-SQL/ORACLE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！