Convert epoch to date in sqlplus / Oracle
31,157
In Oracle, adding X to a DATE will return you a DATE X days later.
If ESTIMATEDENDTIME is milliseconds since Epoch then you could do
DATE '1970-01-01' + ( 1 / 24 / 60 / 60 / 1000) * ESTIMATEDENDTIME
and then use to_char to achieve the correct format of the resulting date. e.g:
SELECT
captureid
, startdate
, enddate
, state
, estimatedendtime
, DATE '1970-01-01' + ( 1 / 24 / 60 / 60 / 1000) * estimatedendtime AS estimatedenddate
FROM recording
Author by
Oink
Updated on July 05, 2022Comments
-
Oink almost 2 years
I have the following table:
SQL> desc recording Name Null? Type -------------------- -------- ------ CAPTUREID NOT NULL NUMBER(9) STARTDATE NOT NULL DATE ENDDATE DATE STATE NUMBER(1) ESTIMATEDENDTIME NUMBER(13)
Here's a single line for this table:
SQL> select * from recording where CAPTUREID=14760457; CAPTUREID STARTDATE ENDDATE STATE ESTIMATEDENDTIME ---------- ------------------- ------------------- ----- ---------------- 14760457 29/09/2010 08:50:01 29/09/2010 09:52:04 0 1285746720000
I'm pretty sure that this has been asked so many times before, but all the solutions I've found so far didn't really work, so... How do I convert
ESTIMATEDENDTIME
from its original epoch form to aDD/MM/YYY HH:MI:SS
format in a single query in SQLPLUS?Thanks!
-
Oink over 13 yearsDamn! That's faster and easier than other solutions I was trying! THANKS!