DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Calculate Timestamp Concurrent Intervals

  1. #1
    Join Date
    Mar 2007
    Posts
    3

    Calculate Timestamp Concurrent Intervals

    I have a table that looks like this

    CREATE TABLE LOG_FIELD_CHECKIN_HISTORY (
    MYID NUMBER(9,0) PRIMARY KEY,
    POLLSITEID NUMBER,
    PWID NUMBER,
    COMPMAC VARCHAR2(25),
    COMPIP VARCHAR2(25),
    SOAPMSG CLOB,
    CHECKINTIME TIMESTAMP
    );


    Field systems will "check in" at set intervals which creates a new record in the table

    I want to create the sql that will show me current intervals within a tolerance(set my the user...ie 20sec..or 60sec) per pollsiteid,per date.

    For instance pollsiteid 100 was online between 3/14 8:00am - 5:00pm, 3/15 8:00am - 8:30am Down 8:31am - 5:00pm

    I have been using this function to diff to timestamps...I'm just not sure how to bundle everything up.


    CREATE OR REPLACE Function timestamp_diff_in_seconds (ts1 in timestamp, ts2 in timestamp)
    return number is total_secs number;
    diff interval day(9) to second(6);
    begin
    diff := ts2 - ts1;
    total_secs := abs(extract(second from diff) + extract(minute from diff)*60 + extract(hour from diff)*60*60 + extract(day from diff)*24*60*60);

    return total_secs;
    end timestamp_diff_in_seconds;
    /

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    You might want to look at either the lead or lag analytic functions.

    http://download-east.oracle.com/docs...s56a.htm#83619

  3. #3
    Join Date
    Mar 2007
    Posts
    3
    Thanks Gandolf!

    So now I created a sql statement like this..

    select pollsiteid,checkintime,LAG(checkintime,1) OVER (order by checkintime) as t2,timestamp_diff_in_seconds(checkintime,LAG(checkintime,1) OVER (order by checkintime)) as secdif from LOG_FIELD_CHECKIN_HISTORY where pollsiteid = 510

    Which gives me the difference between the two records.

    Now..I'm not to sure how to indicate the concurrent intervals..especially between two dates.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by c0dem0nkey
    Thanks Gandolf!

    So now I created a sql statement like this..

    select pollsiteid,checkintime,LAG(checkintime,1) OVER (order by checkintime) as t2,timestamp_diff_in_seconds(checkintime,LAG(checkintime,1) OVER (order by checkintime)) as secdif from LOG_FIELD_CHECKIN_HISTORY where pollsiteid = 510

    Which gives me the difference between the two records.

    Now..I'm not to sure how to indicate the concurrent intervals..especially between two dates.
    Did you know you can subtract one date from another and find out the difference in time.

  5. #5
    Join Date
    Mar 2007
    Posts
    3
    Ha...thanks.

    I guess I'm note sure how to do it in a sql statement. Not so much the subtraction but concurrency.

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by c0dem0nkey
    Ha...thanks.

    I guess I'm note sure how to do it in a sql statement. Not so much the subtraction but concurrency.
    Both of these value represent the same thing. The first shows the hours, minutes and seconds of the current time as a decimal component of 1 day. i.e. since midnight .653946759 days have passed. The second shows the same thing in hours minutes and seconds.

    So when you subtract one date from another you end up with a number, the whole portion of that number represents days, the decimal portion represents part of a day.


    Code:
    SQL> select sysdate-TRUNC(sysdate), to_char(sysdate, 'HH24:MI:SS:SSSS') from dual;
    SYSDATE-TRUNC(SYSDATE) TO_CHAR(SYSDA
    ---------------------- -------------
                .653946759 15:41:41:4141
    Does that help?

Posting Permissions

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


Click Here to Expand Forum to Full Width