oracle convert unix epoch time to date

Question

The context is that there is an existing application in our product which generates and sends the EPOCH number to an existing oracle procedure & vice versa. It works in that procedure using something like this

SELECT UTC_TO_DATE (1463533832) FROM DUAL
SELECT date_to_utc(creation_date) FROM mytable

When I tried these queries it does work for me as well with Oracle 10g server (and oracle sql developer 4.x if that matters).

In the existing procedure the requirement was to save the value as date itself (time component was irrelevant), however in the new requirement I have to convert unix EPOCH value to datetime (at the hours/mins/seconds level, or better in a specific format such as dd-MMM-yyyy hh:mm:ss) in an oracle query. Strangely I am unable to find any documentation around the UTC_TO_DATE and DATE_TO_UTC functions with Google. I have looked around at all different questions on stackoverflow, but most of them are specific to programming languages such as php, java etc.

Bottom line, how to convert EPOCH to that level of time using these functions (or any other functions) in Oracle query? Additionally are those functions I am referring could be custom or specific somewhere, as I don't see any documentation or reference to this.

Answer 1

To convert from milliseconds from epoch (assume epoch is Jan 1st 1970):

select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 / 1000) * 1322629200000
from dual;

11/30/2011 5:00:00 AM

To convert that date back to milliseconds:

select (to_date('11/30/2011 05:00:00', 'MM/DD/YYYY HH24:MI:SS') - to_date('19700101', 'YYYYMMDD')) * 24 * 60 * 60 * 1000
from dual;

1322629200000

If its seconds instead of milliseconds, just omit the 1000 part of the equation:

select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 ) * 1322629200
from dual;

select (to_date('11/30/2011 05:00:00', 'MM/DD/YYYY HH24:MI:SS') - to_date('19700101', 'YYYYMMDD')) * 24 * 60 * 60
from dual;

Hope that helps.

Answer 2

Another option is to use an interval type:

SELECT TO_TIMESTAMP('1970-01-01 00:00:00.0'
                   ,'YYYY-MM-DD HH24:MI:SS.FF'
       ) + NUMTODSINTERVAL(1493963084212/1000, 'SECOND')
FROM dual;

It has this advantage that milliseconds won't be cut.