oracle10g - Convert timestamp datatype into unix timestamp Oracle

Question

I have a timestamp datatype in database with format 24-JuL-11 10.45.00.000000000 AM and want to get it converted into unix timestamp, how can I get it?

Answer 1

This question is pretty much the inverse of Convert Unixtime to Datetime SQL (Oracle)

As Justin Cave says:

There are no built-in functions. But it's relatively easy to write one. Since a Unix timestamp is the number of seconds since January 1, 1970

As subtracting one date from another date results in the number of days between them you can do something like:

create or replace function date_to_unix_ts( PDate in date ) return number is

   l_unix_ts number;

begin

   l_unix_ts := ( PDate - date '1970-01-01' ) * 60 * 60 * 24;
   return l_unix_ts;

end;

As its in seconds since 1970 the number of fractional seconds is immaterial. You can still call it with a timestamp data-type though...

SQL> select date_to_unix_ts(systimestamp) from dual;

DATE_TO_UNIX_TS(SYSTIMESTAMP)
-----------------------------
                   1345801660

---

In response to your comment, I'm sorry but I don't see that behaviour:

SQL> with the_dates as (
  2    select to_date('08-mar-12 01:00:00 am', 'dd-mon-yy hh:mi:ss am') as dt
  3      from dual
  4     union all
  5    select to_date('08-mar-12', 'dd-mon-yy')
  6      from dual )
  7  select date_to_unix_ts(dt)
  8    from the_dates
  9         ;

DATE_TO_UNIX_TS(DT)
-------------------
         1331168400
         1331164800

SQL>

There's 3,600 seconds difference, i.e. 1 hour.