Oracle Date to Unix Timestamp

posted August 23rd 2006 at 1811 EDT in All, Articles, Oracle, Programming, SQL, mysql

Here's a problem : how do you get SYSDATE back as an integer number of seconds from epoc in Oracle.

It turns out it's quite hard .... even though thats how it's stored internally, and number of seconds from epoc is a fairly standard date format.

So here is what I was able to find. It seems way to wordy and I hope one of my readers can come up with an easier solution.


SELECT (sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) AS dt FROM dual; 
 

The value returned is as follows


1156307386
 

You can get the same timestamp format from the linux command date +%s, from the mysql function unix_timestamp or by calling time.time() in python

Credit goes to google for eventually finding this page with the answer

5 Responses

  1. #1 s.meier
    3 years, 3 months ago

    Thank you, very helpful!

  2. #2 Lecocq
    2 years, 10 months ago

    Simple but it was necessary to think about it ;).
    Thank you.

  3. #3 Simon
    2 years, 3 months ago

    (to_date(‘01-jan-1970′, ‘dd-mon-yyyy’) + (d.startime / (86397.94149))) STARTTIME,

    found this to be pretty accurate.

  4. #4 Mark Schrijver
    1 year, 11 months ago

    Seeing how the new timestamp formats works differently when you subtract them, you need a different approach there. I came up with the following:

    select days * (24*60*60*1000) + hours * (60*60*1000) + minutes * (60*1000) + seconds * (1000) + milliseconds unix_time
from (
select to_number(rtrim(substr(dt, 2, instr(dt, ' ')-1))) days,
       to_number(substr(dt, instr(dt, ' ')+1, 2)) hours,
       to_number(substr(dt, instr(dt, ':')+1, 2)) minutes,
       to_number(substr(dt, instr(dt, '.')-2, 2)) seconds,
       to_number(rtrim(substr(dt, instr(dt, '.')+1), 0)) milliseconds,
       dt
from ( select to_char(dt) dt
       from ( select ( current_timestamp - TO_TIMESTAMP('01-JAN-1970','DD-MON-YYYY')) dt
              from dual)));

    This one results in the number of milliseconds since 1-1-1970, with a timestamp as the root date field.

    It’s a bit long and cumbersome, so if anyone has anything better…

  5. #5 Pasupathy
    11 months, 3 weeks ago

    thanks a lot .. i was searching this for half a day .. got it now
    thanks again