5 Replies Latest reply: Feb 5, 2013 12:20 PM by rp0428 RSS

    Number of seconds since 1970 Jan 1 GMT

    ysri
      Hi,
      I want to find the number of seconds passed since 1970/01/01 GMT on the oracle server, provided I don't know the timezone of the oracle server

      The following query:
      SELECT
      systimestamp
      ,sys_extract_utc(systimestamp)
      ,timestamp '1970-01-01 00:00:00 +00:00'
      ,sys_extract_utc(systimestamp)-timestamp '1970-01-01 00:00:00 +00:00'
      ,(sys_extract_utc(systimestamp)-timestamp '1970-01-01 00:00:00 +00:00') * 24 * 60 * 60
      FROM DUAL;

      fails at the last column (which is the only one I need) with message:
      SQL Error: ORA-01873: the leading precision of the interval is too small
      01873. 00000 - "the leading precision of the interval is too small"

      I am looking for a light weight sql to get seconds passed on the oracle server since epoch, when oracle server timezone is not known

      Thanks,
      -sri
        • 1. Re: Number of seconds since 1970 Jan 1 GMT
          rp0428
          >
          I want to find the number of seconds passed since 1970/01/01 GMT on the oracle server, provided I don't know the timezone of the oracle server
          >
          You don't need to know Oracle's timezone; Oracle knows it and includes it in SYSTIMESTAMP.

          1. the number of seconds is the number of days times hours per day times minutes per hour times seconds per minute
          or: seconds = days * 24 * 60 * 60

          2. Use the SYS_EXTRACT_UTC function to adjust Oracle's timestamp to UTC.
          See the SQL Language doc - http://docs.oracle.com/cd/E14072_01/server.112/e10592/functions184.htm

          3. Subtract 1/1/1970 to get the days

          4. Use the formula in #1 to compute the seconds from the days
          SELECT systimestamp, SYS_EXTRACT_UTC(sysTIMESTAMP),
          trunc(sys_extract_utc(systimestamp)) - TO_DATE('01/01/1970', 'MM/DD/YYYY') days,
          (trunc(sys_extract_utc(systimestamp)) - TO_DATE('01/01/1970', 'MM/DD/YYYY')) 
          * 24 * 60 * 60 seconds
           FROM dual 
          
          SYSTIMESTAMP     SYS_EXTRACT_UTC(SYSTIMESTAMP)     DAYS     SECONDS
          2/3/2013 5:15:57.262171 PM -08:00     2/4/2013 1:15:57.262171 AM     15740     1359936000
          • 2. Re: Number of seconds since 1970 Jan 1 GMT
            chris227
            select
            extract (day from t) * 24*60*60
            +
            extract (hour from t) * 60*60
            +
            extract (minute from t) * 60
            +
            extract (second from t) d
            from
            (select 
            sys_extract_utc(systimestamp)-to_date('01011970','DDMMYYYY') t
            from dual)
            
            D
            1359976381,355233
            • 3. Re: Number of seconds since 1970 Jan 1 GMT
              rp0428
              And how does that add anything to what I already posted?
              • 4. Re: Number of seconds since 1970 Jan 1 GMT
                chris227
                rp0428 wrote:
                And how does that add anything to what I already posted?
                Yours is returning only the seconds for the days passed, if i am not wrong, but not the hours, minutes, seconds.
                • 5. Re: Number of seconds since 1970 Jan 1 GMT
                  rp0428
                  Ok - but isn't that what OP asked?
                  >
                  Number of seconds since 1970 Jan 1 GMT