Convert unix timestamp to Date and DateTime - SQL/ORACLE

Question

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.

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;

How can i get a date from this value?

You could cast the result to a date.

Gordon Linoff
– Gordon Linoff

2016-02-24 21:38:28 +00:00
Commented Feb 24, 2016 at 21:38 — Gordon Linoff
– Gordon Linoff, Commented Feb 24, 2016 at 21:38
Yes, Had been trying unsuccessfully so far. TRUNC worked.

Ninja
– Ninja

2016-02-24 22:06:28 +00:00
Commented Feb 24, 2016 at 22:06 — Ninja
– Ninja, Commented Feb 24, 2016 at 22:06

Ninja · Accepted Answer · 2016-02-24 22:06:53Z

2

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;

This worked.

answered Feb 24, 2016 at 22:06

Ninja

1833 gold badges5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert unix timestamp to Date and DateTime - SQL/ORACLE

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related