Convert Timestamp Datatype Into Unix Timestamp Oracle

Answer :

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.

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.

for date:

   FUNCTION date_to_unix (p_date  date,in_src_tz in varchar2 default 'Europe/Kiev') return number is
begin
    return  round((cast((FROM_TZ(CAST(p_date as timestamp), in_src_tz) at time zone 'GMT') as date)-TO_DATE('01.01.1970','dd.mm.yyyy'))*(24*60*60));
end;

for timestamp:

FUNCTION timestamp_to_unix (p_time  timestamp,in_src_tz in varchar2 default 'Europe/Kiev') return number is
    begin
        return  round((cast((FROM_TZ(p_time, in_src_tz) at time zone 'GMT') as date)-TO_DATE('01.01.1970','dd.mm.yyyy'))*(24*60*60));
    end;