Oracle current_timestamp to seconds conversion

Question

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

Answer 1

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

Answer 2

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 ;