Convert UNIX TIMESTAMP to DATE in Oracle


I need to convert a unix timestamp that is stored in a column of data type VARCHAR2 to a data type DATE.

Example .. 1404316382 = 2014-07-02 15: 53: 02.000

Any ideas?

asked by Edgar Conrado 26.04.2016 в 19:12

2 answers


I already found the solution, I'll leave it in case someone else can solve the same problem.

To make the conversion you need the following instruction:

to_char(to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval('1404316382','SECOND'),'YYYY-MM-DD HH24:MI:SS')
answered by 26.04.2016 / 19:12

I leave you another option that can also serve you

SELECT to_char(TO_DATE('1970-01-01','YYYY-MM-DD') + 1404316382  / 86400,'YYYY-MM-DD HH24:MI:SS')
from dual
answered by 25.05.2016 в 22:47