Oracle current_timestamp to seconds conversion

We are using开发者_开发问答 Oracle database.

In our table timestamp is stored as seconds since 1970, how can I convert the time stamp obtained through current_timestamp() function to seconds

This would do it:

select round((cast(current_timestamp as date) - date '1970-01-01')*24*60*60) from dual

Though I wouldn't use current_timestamp if I was only interested in seconds, I would use SYSDATE:

select round((SYSDATE - date '1970-01-01')*24*60*60) from dual

Maybe not completely relevant. I had to resolve other way around problem (e.g. Oracle stores timestamp in V$RMAN_STATUS and V$RMAN_OUTPUT) and I had to convert that to date/timestamp. I was surprised, but the magic date is not 1970-01-01 there, but 1987-07-07. I looked at Oracle's history and the closest date I can think of is when they ported Oracle products to UNIX. Is this right?

Here's my SQL

SELECT /*+ rule */
         to_char(min(stamp)/(24*60*60) + date '1987-07-07', 'DD-MON-YYYY HH24:MI:SS') start_tm
       , to_char(to_char(max(stamp)/(24*60*60) + date '1987-07-07', 'DD-MON HH24:MI:SS')) end_tm
FROM V$RMAN_STATUS 
START WITH (RECID, STAMP) =
     (SELECT MAX(session_recid),MAX(session_stamp) FROM V$RMAN_OUTPUT) 
CONNECT BY PRIOR RECID = parent_recid ;

I needed to send timestamp to GrayLog via GELF from Oracle DB. I tried different versions and solutions but only one worked correctly.

SQL:

SELECT REPLACE((CAST(dat AS DATE) - TO_DATE('19700101', 'YYYYMMDD')) * 86400 + MOD(EXTRACT(SECOND FROM dat), 1), ',', '.') AS millis
FROM (SELECT SYSTIMESTAMP AT TIME ZONE 'GMT' AS dat FROM dual)

The result for Systmiestamp

2018/12/18 19:47:29,080988 +02:00

will be

1545155249.080988