Konvertieren timestamp-Datentyp in unix timestamp, Oracle

Diese Frage ist so ziemlich das umgekehrte von Unixtime konvertieren zu Datetime SQL (Oracle)

Als Justin Höhle sagt:

Gibt es keine built-in-Funktionen. Aber es ist relativ einfach zu schreiben
ein. Da ein Unix-timestamp ist die Anzahl der Sekunden seit dem 1. Januar
1970

Subtraktion eines Datums von einem anderen Datum Ergebnisse in der Anzahl der Tage zwischen Ihnen, die Sie tun können, so etwas wie:

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;

Als seine in Sekunden seit 1970 die Zahl der in Bruchteilen von Sekunden, ist dabei unerheblich. Sie können es immer noch mit einem timestamp-Datentyp-obwohl...

SQL> select date_to_unix_ts(systimestamp) from dual;

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

In Antwort auf Ihren Kommentar, es tut mir Leid, aber ich sehe nicht, dass Verhalten:

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>

Gibt es 3600 Sekunden Unterschied, also 1 Stunde.

Datum:

   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;

für 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;

Das war, was ich kam mit:

select    substr(extract(day from (n.origstamp - timestamp '1970-01-01 00:00:00')) * 24 * 60 * 60 + 
          extract(hour from (n.origstamp - timestamp '1970-01-01 00:00:00')) * 60 * 60 + 
          extract(minute from (n.origstamp - timestamp '1970-01-01 00:00:00')) * 60 + 
          trunc(extract(second from (n.origstamp - timestamp '1970-01-01 00:00:00')),0),0,15) TimeStamp 
          from tablename;

FWIW

Für die Konvertierung von Oracle -, Zeit-und Unix-Zeiten verwende ich diese Funktionen.
Sie überlegen, Ihre aktuelle Zeitzone. Sollten Sie auch hinzufügen DETERMINISTIC Schlüsselwort, zum Beispiel, wenn Sie wie zu verwenden diese Funktion in ein function-based index. Konvertierung zwischen DATE und TIMESTAMP getan werden sollte implizit durch Oracle.

FUNCTION Timestamp2UnixTime(theTimestamp IN TIMESTAMP, timezone IN VARCHAR2 DEFAULT SESSIONTIMEZONE) RETURN NUMBER DETERMINISTIC IS
    timestampUTC TIMESTAMP;
    theInterval INTERVAL DAY(9) TO SECOND;
    epoche NUMBER;
BEGIN
    timestampUTC := FROM_TZ(theTimestamp, timezone) AT TIME ZONE 'UTC';
    theInterval := TO_DSINTERVAL(timestampUTC - TIMESTAMP '1970-01-01 00:00:00');
    epoche := EXTRACT(DAY FROM theInterval)*24*60*60 
        + EXTRACT(HOUR FROM theInterval)*60*60 
        + EXTRACT(MINUTE FROM theInterval)*60 
        + EXTRACT(SECOND FROM theInterval);
    RETURN ROUND(epoche);
END Timestamp2UnixTime;



FUNCTION UnixTime2Timestamp(UnixTime IN NUMBER) RETURN TIMESTAMP DETERMINISTIC IS
BEGIN
    RETURN (TIMESTAMP '1970-01-01 00:00:00 UTC' + UnixTime * INTERVAL '1' SECOND) AT LOCAL;
END UnixTime2Timestamp;