This discussion is archived
5 Replies Latest reply: Feb 5, 2013 10:20 AM by rp0428 RSS

Number of seconds since 1970 Jan 1 GMT

ysri Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    And how does that add anything to what I already posted?
  • 4. Re: Number of seconds since 1970 Jan 1 GMT
    chris227 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Ok - but isn't that what OP asked?
    >
    Number of seconds since 1970 Jan 1 GMT

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points