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.

Kamikamikaze

Those are not standard functions, they are part of your application. You need to find the source for them (in source control; or in user_source or all_source if that doesn't exist). But do you want to modify them, have new versions that include the time, or have standalone conversion? You also need to check if the dates you pass in /get back are also UTC or represent a different region - your functions may be adjusting them. – Sachiko

@AlexPoole Thanks for the suggestion. It did struck to me that they could be custom but I must admit, I didn't realize that they could be custom in my codebase itself. So yes, I found their definition in source control, and they have internal working pretty much same as what tbone has shared below.. – Kamikamikaze

Do they change the date to a different time zone, or do they assume the dates are also representing UTC? And given your comment on tbone's answer, are you sure they don't already set the time components and you just weren't seeing those? – Sachiko

@AlexPoole Here is what they had - RETURN to_date('01.01.70','dd.mm.rr') + (utc_in/(60*60*24)); So I don't think they actually changed to local time, they wanted to store it as UTC as well.. and so do I .. And yes, once I changed formatting in my client, I could see the time coming in their custom function as well – Kamikamikaze

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.

Drinker

Note,this gives correct results only if your local time zone is also UTC. – Rubicon

Thanks @tbone, this is quite close.. The only comment I have is that if I do your query, and even this one - select to_date('19700101', 'YYYYMMDD HH24:MI:SS') + ( 1 / 24 / 60 / 60 ) * 1463533832 from dual; Still in the Sql Developer output window i see this - TO_DATE('19700101','YYYYMMDDH ----------------------------- 18-MAY-16 Any thoughts why don't I see the hour, mins and seconds – Kamikamikaze

@SubhashDike - that is just how your client is displaying the date; change your NLS_DATE_FORMAT or (preferably) use to_char() to specify the format, with a mask like 'YYYY-MM-DD HH24:MI:SS'. – Sachiko

@AlexPoole Great suggestion.. I adjusted my sql developer settings, and here is what I see now, perfect! - TO_DATE('19700101','YYYYMMDDH ----------------------------- 18-MAY-16 01:10:32 – Kamikamikaze

select to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 / 1000) * 1322629200000 from dual; This does not format to hour minutes seconds – Confidence

Hi @DarylF, see Alex Poole's comment above, your client's default format mask for dates omits the hour/min/sec portion. – Drinker

may i know what dual is? – Samora

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.

Limnetic

An epoch is normally in the UTC time zone. Instead of TO_TIMESTAMP, you can either use TO_TIMESTAMP_TZ and add UTC to the string or, simpler, use the timestamp literal TIMESTAMP '1970-01-01 00:00:00 UTC'. – Sacristy

Answer 3

If your epoch time is stored as an integer..... And you desire the conversion to Oracle date format.

Step 1--> Add your epoch date (1462086000) to standard 01-jan-1970. 86400 is seconds in a 24 hour period.

*Select TO_DATE('01-jan-1970',   'dd-mon-yyyy') + 1462086000/86400 from dual*  
   **output is 5/1/2016 7:00:00 AM**

Step 2--> Convert it to a CHAR . This is needed for formatting before additional functions can be applied.

  *Select TO_CHAR(TO_DATE('01-jan-1970',   'dd-mon-yyyy') + 1462086000/86400 ,'yyyy-mm-dd hh24:mi:ss') from dual*

   output is  2016-05-01 07:00:00

Step 3--> Now onto Timestamp conversion

Select to_timestamp(TO_CHAR(TO_DATE('01-jan-1970',   'dd-mon-yyyy') + 1462086000/86400 ,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss') from dual

  output is 5/1/2016 7:00:00.000000000 AM

Step 4--> Now need the TimeZone, usage of UTC

Select from_tz(to_timestamp(TO_CHAR(TO_DATE('01-jan-1970',   'dd-mon-yyyy') + 1462086000/86400 ,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),'UTC')  from dual

     output is 5/1/2016 7:00:00.000000000 AM +00:00

Step 5--> If your timezone need is PST

 Select from_tz(to_timestamp(TO_CHAR(TO_DATE('01-jan-1970',   'dd-mon-yyyy') + 1462086000/86400 ,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),'UTC')  at time zone 'America/Los_Angeles' TZ from dual

           output is 5/1/2016 12:00:00.000000000 AM -07:00

Step 6--> Format the PST Timezone timestamp.

 Select to_Char(from_tz(to_timestamp(TO_CHAR(TO_DATE('01-jan-1970',   'dd-mon-yyyy') + 1462086000/86400 ,'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss'),'UTC')  at time zone 'America/Los_Angeles' ,'DD-MON-YYYY HH24:MI:SS') TZ from dual 

          output is  01-MAY-2016 00:00:00

Step 7--> And finally, if your column is date datatype

   Add to_DATE to the whole above Select. 

Maccarthy

Converting a string into a DATE then into a CHAR and back again to a TIMESTAMP seem to be pointless. – Rubicon

Answer 4

Here it is for both UTC/GMT and EST;

GMT  select (to_date('1970-01-01 00','yyyy-mm-dd hh24') +
     (1519232926891)/1000/60/60/24) from dual;

EST  select new_time(to_date('1970-01-01 00','yyyy-mm-dd hh24') + 
     (1519232926891)/1000/60/60/24, 'GMT', 'EST') from dual;

Fluellen

Answer 5

I thought somebody would be interested in seeing an Oracle function version of this:

CREATE OR REPLACE FUNCTION unix_to_date(unix_sec NUMBER)
RETURN date
IS
ret_date DATE;
BEGIN
    ret_date:=TO_DATE('19700101','YYYYMMDD')+( 1/ 24/ 60/ 60)*unix_sec;
    RETURN ret_date;
END;
/

I had a bunch of records I needed dates for so I updated my table with:

update bobfirst set entered=unix_to_date(1500000000+a);

where a is a number between 1 and 10,000,000.

Matadi

Answer 6

Universal epoch to date conversion function for epoch time in seconds or any fractions of seconds

create or replace function epoch_to_date(epoch in number) return date
as
begin
  if length(epoch) >= 10 then
    return to_date('19700101', 'YYYYMMDD') + ( 1 / 24 / 60 / 60 / power(10,length(epoch)-10) ) * epoch;
  else
    raise_application_error(-20001,'epoch time too short');
  end if;
end;

Skied

Answer 7

A shorter method to convert timestamp to nanoseconds.

SELECT (EXTRACT(DAY FROM (
    SYSTIMESTAMP --Replace line with desired timestamp --Maximum value: TIMESTAMP '3871-04-29 10:39:59.999999999 UTC'
- TIMESTAMP '1970-01-01 00:00:00 UTC') * 24 * 60) * 60 + EXTRACT(SECOND FROM
    SYSTIMESTAMP --Replace line with desired timestamp
)) *  1000000000 AS NANOS FROM DUAL;

NANOS
1598434427263027000

A method to convert nanoseconds to timestamp.

SELECT TIMESTAMP '1970-01-01 00:00:00 UTC' + numtodsinterval(
    1598434427263027000 --Replace line with desired nanoseconds
/ 1000000000, 'SECOND') AS TIMESTAMP FROM dual;

TIMESTAMP
26/08/20 09:33:47,263027000 UTC

As expected, above methods' results are not affected by time zones.

A shorter method to convert interval to nanoseconds.

SELECT (EXTRACT(DAY FROM (
    INTERVAL '+18500 09:33:47.263027' DAY(5) TO SECOND --Replace line with desired interval --Maximum value: INTERVAL '[phone]:39:59.999999999' DAY(6) TO SECOND(9) or up to 3871 year
) * 24 * 60) * 60 + EXTRACT(SECOND FROM (
    INTERVAL '+18500 09:33:47.263027' DAY(5) TO SECOND --Replace line with desired interval
))) * 1000000000 AS NANOS FROM DUAL;

NANOS
1598434427263027000

A method to convert nanoseconds to interval.

SELECT numtodsinterval(
    1598434427263027000 --Replace line with desired nanoseconds
/ 1000000000, 'SECOND') AS INTERVAL FROM dual;

INTERVAL
+18500 09:33:47.263027

As expected, millis, micros and nanos are converted and reverted, dispite of SYSTIMESTAMP doesn't have nanosecounds information.

Replace 1000000000 by 1000, for example, if you'd like to work with milliseconds instead of nanoseconds.

I've tried some of posted methods, but almost of them are affected by the time zone or result on data loss after revertion, so I've decided do post the methods that works for me.

Tacita

If you believe that this answer answers the other 10 questions that you have just posted on, the correct action isn't to post 11 answers, it's to post one answer and flag the other 10 questions as duplicates of this one. – Avestan