Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    46

    Unanswered: difference between two timestamps

    Hi,

    How can I find difference between two timestamps. It should be precise in milliseconds. Considering change in day, month, year, century.

    Thanks

    Prashant

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I would subtract one from the other.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    1* select systimestamp - add_months(systimestamp,-4) from dual
    SQL> /

    SYSTIMESTAMP-ADD_MONTHS(SYSTIMESTAMP,-4)
    ---------------------------------------------------------------------------
    +000000122 00:00:00.291000


    122 days
    00 hours
    00 minutes
    00 seconds
    291000 milliseconds
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Mar 2004
    Posts
    46
    Thanks anacedent and beilstwh,

    what datatypes does it return?
    Is there any function to extract each (days, hour, etc.) or I have to do string manipulation?

    Prashant

  5. #5
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You just said it.. the function is EXTRACT (only on 9i and above).
    Code:
    SQL> select extract( day from systimestamp - add_months(systimestamp,-4) ) from dual;
    
    EXTRACT(DAYFROMSYSTIMESTAMP-ADD_MONTHS(SYSTIMESTAMP,-4))
    --------------------------------------------------------
                                                         122

  6. #6
    Join Date
    Mar 2004
    Posts
    46
    Thanks JMartinez.

    can anynody review the function?

    create or replace function timestamp_diff
    (
    start_time_in timestamp
    , end_time_in timestamp
    )
    return number
    as
    l_days number;
    l_hours number;
    l_minutes number;
    l_seconds number;
    l_milliseconds number;
    begin
    select extract(day from end_time_in-start_time_in)
    , extract(hour from end_time_in-start_time_in)
    , extract(minute from end_time_in-start_time_in)
    , extract(second from end_time_in-start_time_in)
    into l_days, l_hours, l_minutes, l_seconds
    from dual;

    l_milliseconds := l_seconds*1000 + l_minutes*60*1000
    + l_hours*60*60*1000 + l_days*24*60*60*1000;

    return l_milliseconds;
    end;

  7. #7
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122

    why

    Why?

    1* SELECT timestamp_diff('03-JUL-06 10.32.24.000000', '04-JUL-06 10.32.25.291000') FROM dual
    SQL> /

    TIMESTAMP_DIFF('03-JUL-0610.32.24.000000','04-JUL-0610.32.25.291000')
    --------------------------------------------------------------------------------
    Days 1 Hours 0 Minutes 0 Seconds 1 Milliseconds 291000


    Code:
    CREATE OR REPLACE FUNCTION timestamp_diff
    (
    start_time_in TIMESTAMP
    , end_time_in TIMESTAMP
    )
    -- RETURN NUMBER
    RETURN VARCHAR 
    AS
    l_days NUMBER;
    l_hours NUMBER;
    l_minutes NUMBER;
    l_seconds NUMBER;
    l_milliseconds NUMBER;
    BEGIN
    SELECT extract(DAY FROM end_time_in-start_time_in)
    , extract(HOUR FROM end_time_in-start_time_in)
    , extract(MINUTE FROM end_time_in-start_time_in)
    , extract(SECOND FROM end_time_in-start_time_in)
    INTO l_days, l_hours, l_minutes, l_seconds
    FROM dual;
    
    --l_milliseconds := l_seconds*1000 + l_minutes*60*1000 + l_hours*60*60*1000 + l_days*24*60*60*1000;
    --RETURN ' Milliseconds ' || l_milliseconds;
    
    l_milliseconds := (l_seconds - FLOOR(l_seconds) ) * 1000000 ; -- + l_minutes*60*1000 + l_hours*60*60*1000 + l_days*24*60*60*1000;
    RETURN 'Days '|| l_days ||' Hours '|| l_hours||' Minutes '||l_minutes||' Seconds '||FLOOR(l_seconds)||' Milliseconds '|| l_milliseconds;
    END;
    Last edited by wrwelden; 07-03-06 at 13:28.

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Why? Because.
    Code:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production
    CORE    10.2.0.1.0      Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production
    
    SQL> SELECT timestamp_diff('03-JUL-06 10.32.24.000000',
      2                        '04-JUL-06 10.32.25.291000')
      3  FROM dual;
    SELECT timestamp_diff('03-JUL-06 10.32.24.000000',
           *
    ERROR at line 1:
    ORA-00904: "TIMESTAMP_DIFF": invalid identifier
    
    
    SQL>
    Where did you find TIMESTAMP_DIFF? Is it, perhaps, your-site-specific function, created by one of your developers? Or is your Oracle database release higher than mine so that it supports this function? I searched for it on the OTN and found nothing.

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    It is a standalone function that you load into your database. The funtion followed his initial code fragment. Hint look in the gray area labeled code.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Thank you, Bill.

    Of course ... stupid me. I saw this code, but - as I didn't read it carefully - I guessed that Wrwelden only formatted Prashant's function and quoted it. "Why" was (in my head) translated as "why are you bothering with your own functions when there's TIMESTAMP_DIFF function available in Oracle?"

    I apologize, Wrwelden ...

  11. #11
    Join Date
    Oct 2004
    Location
    Oklahoma City OK -
    Posts
    122
    No offense taken.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •