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

I realize an answer has already been accepted, but I think it should be made clear that the function in that answer doesn't consider the passed in date's time zone offset. A proper Unix timestamp should be calculated at GMT (+0). Oracle's to_date function assumes the passed in date is in the local time zone unless otherwise specified. This problem is exacerbated by the fact that Daylight Saving Time is a real thing. I over came this problem with the following function:

create or replace
  function unix_time_from_date
      (
        in_date   in date,
        in_src_tz in varchar2 default 'America/New_York'
      )
    return integer
  as
    ut      integer       := 0;
    tz      varchar2(8)   := '';
    tz_date timestamp with time zone;
    tz_stmt varchar2(255);
  begin
    /**
     * This function is used to convert an Oracle DATE (local timezone) to a Unix timestamp (UTC).
     *
     * @author James Sumners
     * @date 01 February 2012
     *
     * @param in_date An Oracle DATE to convert. It is assumed that this date will be in the local timezone.
     * @param in_src_tz Indicates the time zone of the in_date parameter.
     *
     * @return integer
     */

    -- Get the current timezone abbreviation (stupid DST)
    tz_stmt := 'select systimestamp at time zone ''' || in_src_tz || ''' from dual';
    execute immediate tz_stmt into tz_date;
    select
      extract(timezone_abbr from tz_date)
    into tz
    from dual;

    -- Get the Unix timestamp
    select
      (new_time(in_date, tz, 'GMT') - to_date('01-JAN-1970', 'DD-MM-YYYY')) * (86400)
    into ut
    from dual;

    return ut;
end unix_time_from_date;

I have some companion functions, unix_time and unix_time_to_date, available at http://jrfom.com/2012/02/10/oracle-and-unix-timestamps-revisited/. I can't believe Oracle has made it all the way to 11g without implementing these.