15

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

Sirish
  • 9,183
  • 22
  • 72
  • 107
  • possible duplicate of [Oracle SQL - Column with unix timestamp, need dd-mm-yyyy timestamp](http://stackoverflow.com/questions/2401396/oracle-sql-column-with-unix-timestamp-need-dd-mm-yyyy-timestamp) – APC May 04 '11 at 11:48

3 Answers3

29

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
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • 4
    I would add `ROUND()` around that - otherwise you can get a result like `1425030807.000000000000000000000000000004`. Also, doesn't everyone know that there are 86,400 seconds in a day? ;) – David Faber Feb 27 '15 at 14:54
  • 4
    Warning: Unix time is GMT/UTC and Oracle sysdate is the local time. So not sure if this solution will be exact – roblogic Nov 07 '16 at 23:33
1

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 ;
Tagar
  • 13,911
  • 6
  • 95
  • 110
  • 1
    Been in the exact same situation. I calculated the initial date of Oracle as "1987-05-26 00:00:00". – saygley Sep 28 '20 at 10:27
0

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
Aldis
  • 439
  • 4
  • 10