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;
/

You might want to look at either the lead or lag analytic functions.

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

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.

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.

Ha...thanks.

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

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?